Handling conditional flow in Load Script

[lost_rabbit]

I have tables Tab_X, Tab_Y, Tab_Z

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.

[pabloviera · Accepted solution]

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

Regards