Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Need help to create below resultant table.
I have 2 tables of same structure, Table FT & ST
FT:
Category | Product | Value |
A | P1 | 100 |
B | P2 | 200 |
C | P3 | 150 |
D | p3 | 150 |
ST:
Category | Product | Value |
A | P1 | 100 |
B | P4 | 450 |
C | p3 | 150 |
I need 3rd Resultant table where I need records that exist in ST but not in FT based on Category & Product field value match.
Resultant:
Category | Product | Value |
B | P4 | 450 |
C | p3 | 150 |
Sample excel worksheet is also attached.
Thanks in advance.
Raja
Try this:
FT:
LOAD
Category,
Product,
Value,
AutoNumberHash128(Category, Product, Value) as Key
FROM
FT_Source
;
ST:
LOAD
Category,
Product,
Value
FROM
ST_Source
;
Resultant:
LOAD
Category,
Product,
Value
RESIDENT
FT_Source
WHERE
NOT Exist(Key, AutoNumberHash128(Category, Product, Value) )
;
Or may be this:
FT:
LOAD *,
AutoNumber(Category&Product) as Key
INLINE [
Category, Product, Value
A, P1, 100
B, P2, 200
C, P3, 150
D, p3, 150
];
ST:
LOAD * INLINE [
Category, Product, Value
A, P1, 100
B, P4, 450
C, p3, 150
];
Resultant:
NoConcatenate
LOAD Category,
Product,
Value
RESIDENT ST
WHERE NOT Exists(Key, AutoNumber(Category&Product));
DROP Table FT, ST;