Table Tab_X has columns -> a,b,c ( Table Tab_X is going to be the main/primary table)
Table Tab_Y has columns -> p,q,r,s
Table Tab_Z has columns -> x,y,z
The requirement is to have all the rows of Tab_X and use the columns from Tab_Y and Tab_Z based on IF conditions
Say Tab_X has 1000 rows
Left join Tab_X with Tab_Y on Tab_X .a = Tab_Y .p and if value of q is "yes", then use columns r,s. If value of q is “no” then don’t use values from these columns
Eg. If 700 rows in Tab_X match with Tab_Y the result would be, i.e. Tab_X would have columns a,b,c,r,s
For the remaining 300 rows, join with Tab_Z on Tab_X.a= Tab_Z.x and use values from the columns y,z
Tab_X would have 5 columns in total where the values for the last two columns are pulled from Either Tab_X or Tab_Y based on condition
How to do this in Load Script ? Because this is just an example and I must apply the logic for 5 tables and multiple IF conditions.
Hi, the most straightforward way I can think of is joining all the tables and the generating a final table with the values you need. Supposing the 3 tables are loaded previously in the script:
join(X)
LOAD p as a,
q,r,s
Resident Y;
join(X)
LOAD x as a,
y,z
Resident Z;
FinalX:
Load a,b,c,
if(q='yes',r,y) as column4,
if(q='yes',s,z) as column5
Resident X;
drop tables X,Y,Z
Another way this could be easily done is with ApplyMap
Hi, the most straightforward way I can think of is joining all the tables and the generating a final table with the values you need. Supposing the 3 tables are loaded previously in the script:
join(X)
LOAD p as a,
q,r,s
Resident Y;
join(X)
LOAD x as a,
y,z
Resident Z;
FinalX:
Load a,b,c,
if(q='yes',r,y) as column4,
if(q='yes',s,z) as column5
Resident X;
drop tables X,Y,Z
Another way this could be easily done is with ApplyMap
