Handling two profiles for a cutomer ID

[#1]

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:

[swuehl]

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;