0
$\begingroup$

I'm following an example provided in the following

Stack Exchange Link

but, using a different approach. My data is contained in an Excel workbook called "Input Workbook.xlsx" as shown here:

Excel workbook image

I define my working directory as follows:

setDir := Quiet@Check[SetDirectory@DirectoryName@$InputFileName, SetDirectory@NotebookDirectory[]]

path = FileNameJoin[{setDir, "Input Workbook.xlsx"}]

I define my first and second tables as follows:

firsttable = SemanticImport[path, <|"Wine" -> Automatic, "Brand" -> Integer|>, HeaderLines -> 1]

secondtable = SemanticImport[path, <|"Brands" -> Integer, "Color" -> String, "Bouquet" -> String|>, HeaderLines -> 1] // Take[#, 5] &


rls = Append[# :> {##2} & @@@ Normal[secondtable], _ -> {None, None}] // Dispatch

But, then I have to make the following extra steps to get the final output table (with the column headings):

wine = Lookup[Normal[firsttable], "Wine"]

brand = Lookup[Normal[firsttable], "Brand"]

need = Transpose[{wine, brand}]

ds = Dataset[Join[need, Replace[Normal[firsttable[[All, 2]]], rls, {1}], 2]]

finaltable = ds[All, <|"Wine" -> 1, "Brand" -> 2, "Color" -> 3, "Bouquet" -> 4|>]

The point that I'm making is that the above semantic imports should facilitate performing the VLookUp but, somehow; introducing the keys (i.e.; "Wine", Brand", "Brands", "Color", "Bouquet") complicate matters. There must be a much straight-forward way to do a VLookUp when Semantic Import and keys are used. Can anyone show me a better way? Thank you!

$\endgroup$
  • 1
    $\begingroup$ Please provide an example dataset that can be easily copied, together with the expected output. Also, is any of the code in the question necessary to answer the question? (i.e. do we need to know how the dataset is generated, or do you only care about how to do a given query on the final dataset?) $\endgroup$ – Lukas Lang Aug 20 '19 at 18:53
  • $\begingroup$ Just a note, there's no need to use SetDirectory in setDir. That might cause you grief in the future and you can just use the path you have. $\endgroup$ – b3m2a1 Aug 20 '19 at 19:52
  • $\begingroup$ I don't have a website to make the input Excel workbook available @Lukas Lang. That's why I inserted the image of the workbook above; the dataset can be easily built using Excel. The "SetDir" and "path" are necessary and are not a source of trouble b3m2a1. Thank you. $\endgroup$ – Gilmar Rodriguez Pierluissi Aug 21 '19 at 13:06
  • $\begingroup$ Please elaborate on what a VLookup actually is - what the functionality you want to see is. $\endgroup$ – Carl Lange Aug 21 '19 at 15:10
  • $\begingroup$ The Excel VLookup functionalty was described in the Stack Exchange link that I provided at the beginning of my request @Carl Lange. Thank you all for your help! $\endgroup$ – Gilmar Rodriguez Pierluissi Aug 21 '19 at 16:02
0
$\begingroup$
   setDir := Quiet@Check[SetDirectory@DirectoryName@$InputFileName, SetDirectory@NotebookDirectory[]]

   path = FileNameJoin[{setDir, "Input Workbook.xlsx"}]

   firsttable = SemanticImport[path, <|"Wine" -> Automatic, "Brand" ->  Integer|>, HeaderLines -> 1]

   secondtable = SemanticImport[path, <|"Brands" -> Integer, "Color" -> String, "Bouquet" -> String|>, HeaderLines -> 1] // Take[#, 5] &

   thirdtable = SortBy[Dataset[JoinAcross[firsttable, secondtable, "Brand" -> "Brands", "Left"] ], First]

   finaltable = thirdtable[All, {"Wine", "Brand", "Color", "Bouquet"}] /. 
   Missing["Unmatched"] -> "None"
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.