Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;