Forum Discussion

Precy's avatar
Precy
New Contributor
5 years ago

Need to do lookup on multiple tables without using join

I have 30 tables to lookup on 30 respective columns,

In case of other ETL tool we had lookup as a function to do it independently in a single transform.

In snaplogic, I am not sure how to do it. I can combine all 30 into a single lookup table like

Soure_column, Target_Column, Lookup field

But again, if I use this in sql server lookup, and return Target_column as output field for each 30 cross refrence its working as and operator and giving me null.

Can someone help me with how I can crack this in a simple way, since I do not want to do 30 joins.

pipeline

3 Replies

  • Supratim's avatar
    Supratim
    Contributor III
    5 years ago

    @Precy - As per my design-

    1. Create one single pipeline which will accept table name,query and sql account from pipeline param. This pipeline can have only single SQL snap.
    2. From parent pipeline , use json generator/mapper/file to hold those 3 param mention in point -1.
    3. Use pipeline execute snap (can use pool size 20/30 based on the snap infrastructure you have)
    4. After pipeline execute use groupby N and make grp size 0 and then use $tableList.reduce((accum, curr) => curr.merge(accum), {}) , here tableList is target field in groupBy N snap. It will merge all unique column in single row.

    Hope this might solved your issue.

  • Precy's avatar
    Precy
    New Contributor
    5 years ago

    @ Supratim,

    I am little confused with above explanation. Is it possible for you to showcase this with an example please

    • Precy's avatar
      Precy
      New Contributor
      5 years ago

      I will give you an example how i want the result as,

      1. From my input i have 2 fields assettype and ITEMSETID on which i need to do cross reference.

      2. I will have the 2nlookup tables merged as below:

      1. How can i achieve target_column for corresponding attributes when i do lookup on source column without using 2 joins.