Doing a Lookup In SnapLogic? [Help]

[JPaps]

Hi,

I have the following use-case and I haven’t found any way to get it done within SnapLogic. I am wondering if it is possible at all using this tool.

I have a CSV File in Snaplogic Designer with the following fields (example) $ID, $Name, $AmountPaid1, $AmountPaid2.

I also have an Excel File with the following fields: $ID, $AmountPaid2.

I want to alter the original CSV file by “looking up” the excel file and replacing the value of $AmountPaid2 (in the CSV) with the value of $AmountPaid2 from the excel file, only where $ID from the excel = $ID from the CSV.

Any suggestions?

[ptaylor]

Ok, that’s the output that’s clearly not what you want. But if your join isn’t working the way you expect, what you need to study are its inputs. And maybe share them here so we can help diagnose the issue.

[JPaps]

Thanks, thats good advice. I have looked at the very first join that I am doing and I have observed that it is returning all nulls in the preview data. I have switched it to an inner join and I have found that no preview data is being returned after validating. I am certain that there are matching IDs in the documents that I am trying to join.

When validating a Join snap set to use “Inner Join” does Snaplogic only attempt to join data of the top 50 records of each document? Or should I see a preview of 50 successfully joined records from the two documents?

[ptaylor]

Only the first 50 documents of each input view. But try changing Preview Document Count from 50 to 2000 in User Settings.

[epearson]

One thing you could do is instead of trying to join on those 4 different fields, you could use a mapper to create a single ‘key’ field (just concatenate all the values that make up the unique key). Do this for both sources and then do the join on that single key field. You can add a sort in their for good measure if you want, though I think joins support unsorted data. If you need to, write the output at this point just before the join into a file and make sure the keys are matching exactly between the two data sets. All it takes is one tiny difference between what you are trying to join on between your data sets for it to be off, including if a field is an integer object or a string.

[JPaps]

Thanks everyone for your help. @bojanvelevski 's solution works. The issue I was facing was that the join was failing due to the ID’s in both files not matching.

The output document’s ID’s, derived from the CSV files were encapsulated with quotes (") where as the excel didn’t have these. Data types were both text so for example, the join was looking to match ID: 001 with ID: “001”, this would fail. Once I sorted this issue out by doing a replaceAll() function in the join snap, the solution worked flawlessly.

Thank you @ptaylor for pointing me in the right direction to check my data. In this case I triple checked it. I can’t believe that I missed that lol.