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.

@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.

@ Supratim,

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

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:

image

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