Handling two profiles for a cutomer ID

Tue, 10 Nov 2015 07:51:28 GMT

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:

Re: Handling two profiles for a cutomer ID

swuehl — Tue, 10 Nov 2015 10:46:14 GMT

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;

topic Handling two profiles for a cutomer ID in QlikView

Handling two profiles for a cutomer ID

Re: Handling two profiles for a cutomer ID