Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two tables and the 2nd table left join the first one. My question is how to change value in Quality based on value in Product?
For example, if Product name starts with "C", change value of Quality to "Bad". Please let me know. Thanks.
Table A | ||
Prod ID | Product | Type |
1 | Apple | Fruit |
2 | Banana | Fruit |
3 | Bike | Vehicle |
4 | Car | Vehicle |
5 | Cat | Animal |
Table B | ||
Type | Source | Quality |
Fruit | USA | Amazing |
Vehicle | China | Great |
Animal | Mexico | Awesome |
Maybe use a MAPPING approach:
Don't join - use Applymap instead
MAP:
MAPPING
LOAD Type, Source & '|' & Quality
FROM TableB;
LOAD
[PROD ID], Product, Type,
If(Left(Product), 'C', 'Bad', SubField(ApplyMap('MAP', Type),'|',2) ) as Quality,
SubField(ApplyMap('MAP', Type),'|',1) ) as Source
FROM TABLEA;
Maybe use a MAPPING approach:
Don't join - use Applymap instead
MAP:
MAPPING
LOAD Type, Source & '|' & Quality
FROM TableB;
LOAD
[PROD ID], Product, Type,
If(Left(Product), 'C', 'Bad', SubField(ApplyMap('MAP', Type),'|',2) ) as Quality,
SubField(ApplyMap('MAP', Type),'|',1) ) as Source
FROM TABLEA;
I added null() to formula since I do have some blank data but otherwise the solution is perfect! Thanks!