Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Experts,
I have a common table, which is formed by Concatenation of two Load Scripts.
Price:
Load * Inline [
Old Account, Account Name, Price
100, John, $500
200, Mary, $350
300, Alex, $0
400, Harry,$1050
500, King , $700
]:
CONCATENATE
Load * Inline [
New Account, Account Name, Price
1000, John, $450
2000, Mary, $0
3000, Alex, $0
4000, Harry, $1200
5000, King , $700
]:
Criteria:
Expected Outcome:
Old Account | New Account | Account Name | Price |
100 | 1000 | John | $450 |
200 | 2000 | Mary | $350 |
300 | 3000 | Alex | $0 |
400 | 4000 | Harry | $1,200 |
500 | 5000 | King | $700 |
Any help would be appreciated! 🙂
Regards!
Hi @dmohanty, you can do that with this script:
tmpPrice:
Load * Inline [
Old Account, Account Name, OldPrice
100, John, $500
200, Mary, $350
300, Alex, $0
400, Harry,$1050
500, King , $700
];
Outer Join // Merge in the same row by Account Name
Load * Inline [
New Account, Account Name, NewPrice
1000, John, $450
2000, Mary, $0
3000, Alex, $0
4000, Harry, $1200
5000, King , $700
];
Price:
LOAD
[Old Account],
[New Account],
[Account Name],
If(not IsNull(NewPrice) and NewPrice<>'$0', NewPrice, OldPrice) as Price
Resident tmpPrice;
DROP table tmpPrice;