Join tables based on condition

[User12321]

Hi,

I have 2 tables and I want to join table 2 to table 1 on material such that:

1. Unit joined is KG.

2. If KG is not available, then unit joined is G.

3. If G is not available, then leave as blank.

How can I do this in the load script? 

Any suggestion is greatly appreciated. Thanks!

[Digvijay_Singh · Accepted solution]

Something like this may be - 

Table1:
Load * inline [
Material, Info1
1, x
2,y
3,z
2,y
1, x ];

left Join(Table1)
Load *, Unit&'|'&Info2 as UnitInfo inline [
Material, Unit, Info2
1,KG,a
2,KG,b
2,G,c
3,BTL,d
3,G,e
]
;

Left Join(Table1)
Load Material,
Count(Unit) as UnitCount,
','&Concat(Unit,',') as UnitCollection
Resident Table1
Group By Material;

Final:
Load Material,Info1, Unit,Info2
resident Table1
Where (wildmatch(UnitCollection,'*KG*') and Unit='KG') or (wildmatch(UnitCollection,'*,G*') and not wildmatch(UnitCollection,'*KG*') and Unit='G')
;
Drop Table Table1;