2 Tables Join Issue

[Vasu2]

I need to combine 2 Tables Data.

Table1:

LOAD * inline [

Material| Type

M1|abcd

] (delimiter is '|');

Table2:

LOAD * inline [

Material|Unit|Unit2

M1|EA|IN_EA

M1|IP|IN_IP

M1|MP|IN_MP

] (delimiter is '|');

Output is coming as below:

Expected output is:

Material	IP UOM	MP UOM	EA UOM
M1	IN_IP	IN_MP	IN_EA

IP_UOM  =if(Unit='IP’,Unit2)

EA_UOM =if(Unit='EA',Unit2)

MP_UOM =if(Unit='MP',Unit2)

I tried all the joins(Left, Right and Inner) but none of them is working.

Any suggestions?

[vinieme12 · Accepted solution]

Wrap the Calculated Dimensions in AGGR()

IP_UOM  =  Aggr(  MaxString({<Unit={'IP'}>} Unit2) , Material )

EA_UOM  =  Aggr(  MaxString({<Unit={'EA'}>} Unit2) , Material )

MP_UOM  =  Aggr(  MaxString({<Unit={'MP'}>} Unit2) , Material )

[Vasu2 · Accepted solution]

Thanks Vineeth For reply.

This fix is working perfectly.

[Vasu2]

Hi Vineeth,

After combing the 10 Tables data we want to this data exported to some location.

hence we are combing all this data into one single table.

can we do this Aggregation at script level? if yes, can you share some example code please?

Thanks in Advance.

[vinieme12 · Accepted solution]

as below

temp_Table2:

LOAD * inline [

Material|Unit|Unit2

M1|EA|IN_EA

M1|IP|IN_IP

M1|MP|IN_MP

] (delimiter is '|');

Load Material
,MaxString(if(Unit='IP’,Unit2)) as IP_UOM
,MaxString(if(Unit='EA’,Unit2)) as EA_UOM
,MaxString(if(Unit='MP’,Unit2)) as MP_UOM
Resident temp_Table2
Group by Material;

Drop table temp_Table2;