Row Data in Column

[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?

[jbhappysocks]

Hi

A little bit depending on how much you simplified your data you can use the generic load. I added Type as a part of the connection here just to show how it works with more than just Material as Key.

Table1:

LOAD * inline [

Material| Type

M1|abcd
M1|efgh
] (delimiter is '|');

Table2:
Generic
load
Material,
Type,
Unit&'_UOM' as Attribute,
Unit2 as Value;

LOAD * inline [

Material|Type|Unit|Unit2

M1|abcd|EA|IN_EA

M1|abcd|IP|IN_IP

M1|abcd|MP|IN_MP

M1|efgh|EA|IN_EA2

M1|efgh|IP|IN_IP2

M1|efgh|MP|IN_MP2

] (delimiter is '|');

for each Unit in 'EA','IP','MP';
left join (Table1)
load * Resident Table2.$(Unit)_UOM;

drop Table Table2.$(Unit)_UOM;
next Unit;

[sidhiq91 · Accepted solution]

@Vasu2 Please see the code below:

NoConcatenate
Table1:
LOAD * inline [

Material| Type

M1|abcd

] (delimiter is '|');

left join (Table1)
Table2:

LOAD Unit2 as [EA UOM]

inline [

Material|Unit|Unit2

M1|EA|IN_EA

M1|IP|IN_IP

M1|MP|IN_MP

] (delimiter is '|')

where Unit='EA';

left join (Table1)
Table3:

LOAD Unit2 as [IP UOM]

inline [

Material|Unit|Unit2

M1|EA|IN_EA

M1|IP|IN_IP

M1|MP|IN_MP

] (delimiter is '|')

where Unit='IP';

left join (Table1)
Table3:

LOAD Unit2 as [MP UOM]

inline [

Material|Unit|Unit2

M1|EA|IN_EA

M1|IP|IN_IP

M1|MP|IN_MP

] (delimiter is '|')

where Unit='MP';

Drop field Type from Table1;

Exit Script;

If this resolves your issue, please like and accept it as a solution.