Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have two tables
Table1: | ||
Customer | Profile | TS |
100 | 1 | 7/28/2015 11:54 |
100 | 2 | 9/20/2015 10:42 |
200 | 3 | 9/28/2015 10:44 |
200 | 4 | 9/28/2015 10:55 |
500 | 5 | 10/28/2015 10:45 |
Table2: | ||
Customer | Status | |
100 | In prog | |
200 | Cancel | |
500 | Completed | |
Output: | ||
Profile | Status | |
1 | Error | |
2 | In Prog | |
3 | Error | |
4 | Cancel | |
5 | Completed |
In the above Scenario:
If the customer has two profiles for EX:100 have two profiles 1 and 2
but we need to show old profile status as error and for the new one Status should shown as per table 2,(which means the profile1 has an error so we are using profile2 that we need to display in our output)
if the customer has one profile, status should shown as per table 2:
Maybe like this:
SET TimestampFormat='M/D/YYYY h:mm';
Table1:
LOAD * INLINE [
Customer, Profile, TS
100, 1, 7/28/2015 11:54
100, 2, 9/20/2015 10:42
200, 3, 9/28/2015 10:44
200, 4, 9/28/2015 10:55
500, 5, 10/28/2015 10:45
];
Table2:
LEFT JOIN
LOAD * INLINE [
Customer, Status
100, In prog
200, Cancel
500, Completed
];
NoConcatenate
LOAD Customer,
Profile,
TS,
if(Peek(Customer)=Customer, 'Error',Status) as Status
Resident Table1
ORDER BY Customer, TS Desc;
DROP Table Table1;