Forum Discussion

JPaps's avatar
JPaps
New Contributor III
5 years ago
Solved

Doing a Lookup In SnapLogic? [Help]

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?

pipeline
transform

16 Replies

    • JPaps's avatar
      JPaps
      New Contributor III
      4 years ago

      Thanks very much for your help @bojanvelevski . I have used the example in the sample pipeline that you sent and strangely enough, my output document is unchanged. The only difference is that I am joining on two fields instead of 1. I am stumped as to what is preventing this from working.

      • ptaylor's avatar
        ptaylor
        Employee
        4 years ago

        It would help to tell us why you’re joining on another field and which field, though it looks like $AmountPaid2 is the only other field you could join on. But including that field in the join does not make sense based on your description – you want to replace the value of $AmountPaid2 in the CSV because the $AmountPaid2 value in the Excel file is different, right? Joining only on $ID should be exactly what you need. The pipeline that @bojanvelevski provided does exactly what you asked, does it not?

  • JPaps's avatar
    JPaps
    New Contributor III
    4 years ago

    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.

    • ptaylor's avatar
      ptaylor
      Employee
      4 years ago

      I would have expected the CSV Parser to remove those double quotes. But I’m glad you figured it out.

      • ptaylor's avatar
        ptaylor
        Employee
        4 years ago

        BTW, where/how did you put the replaceAll in the Join snap settings?

  • epearson's avatar
    epearson
    New Contributor III
    4 years ago

    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.