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]

I see, thanks. Can you share more details? Otherwise there’s nothing to go on to help you.

[JPaps]

Sure, I am not sure what details would help, I will try to share as much as I can, please see below.

Output document is derived from a series of CSV files. Left outer joins are done on each file and the resulting data’s fields are manipulated at the mapper with the red streak above it. The CSV files do not have identical fields. Most of them only have a couple fields in common (the unique identifiers) and I have not padded the missing fields of each file with null columns. The ID and COV fields that I am joining onto the main output document exists inside the document stream and not actually in a file, these ID and COV fields are created inside the mapper with the red streak above it. The circled join snap and the mapper after it is where I am implementing @bojanvelevski 's logic.

Below is the configuration of the circled join snap. I need to use bojan’s logic twice which is why there are two joins and two excels.

Below is the configuration of the mapper immediately after the circled join snap. It is not currently using the logic provided by bojan, I have changed it to what you see in the below pic after observing that bojan’s logic didn’t work for me but what I have now isn’t working either. The target path contains the names of the fields that i need to replace.

Please let me know if I can share any more to help.

[ptaylor]

Yes, that is a lot more complicated than your original example. You’ve got a 3-way join, and you’re not joining on two fields, you’re joining on four! Two fields from the CFRI input and two from the CFCVG input. So are all three input streams sorted on both of the keys (TAIX-POL and TAIX-COV)?

BTW, you’ve got two snaps labelled Join2. Might help to change one so the labels are all distinct.

[ptaylor]

Also, are you able to validate this pipeline so you can preview the input and output data at each step? That can really help diagnose issues like this.

[JPaps]

Thanks, yes you are correct; I am joining 4 fields. I have all 3 input streams sorted ascending on the keys (TAIX-POL and TAIX-COV). I will change the name of the Join2 snap as well.

I can validate the pipeline but the majority of the preview data is null, i think this is because of the multiple left outer joins that I used to derive the output document. So its hard for me to troubleshoot. Below is a screenshot of the preview output data of the “Map Replacement Values” Mapper snap (where I assign the values of the fields in the excels to the fields in the output document)

Edited (forgot to upload screenshot):