Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have data that looks like this:
And need to make it look like this:
I've tried several script iterations such as:
Load
Customer Number,
If(Application='App1','Y') as App1,
If(Application='App2','Y') as App2,
If(Application='App3','Y') as App3
From CustomerApps;
But that places the apps on separate rows per customer number.
I also tried running the script one App at a time like this:
Load
Customer Number,
If(Application='App1','Y') as App1
From CustomerApps;
Join
Load
Customer Number,
If(Application='App2','Y') as App2
From CustomerApps;
Join
Load
Customer Number,
If(Application='App3','Y') as App3
From CustomerApps;
But that too gives me a Cartesian product.
One last attempt at loading the data straight like this:
Load
Customer Number,
Application
From CustomerApps;
Then creating the fields in columns in a straight table such as If(Application='App1','Y'), If(Application='App2,'Y') and If(Application='App3','Y') didn't work either. It only displayed apps when either App1, App2 or App3 was selected.
It sure seems like I did this before but can't figure it out. Hopefully, one of you can get me there quickly.
--john
I'm sure there's more elegant ways of doing this but here's one option.
(Also, it might be easier just creating a pivot table in the front end with the Application field as a dimension as well and set it to horizontal.)
Anyway:
T1:
LOAD * INLINE [
CustomerNumber, Application
1, App1
1, App2
2, App1
2, App2
2, App3
3, App2
3, App3
4, App1
4, App2
4, App3
5, App1
5, App2
5, App3
6, App1
6, App3
];
Load
CustomerNumber,
if(index(AppString,'App1')>0,'Y') as App1,
if(index(AppString,'App2')>0,'Y') as App2,
if(index(AppString,'App3')>0,'Y') as App3;
Load
CustomerNumber,
Concat(Application, ';') as AppString
Resident T1
group by CustomerNumber;
Drop table T1;
I'm sure there's more elegant ways of doing this but here's one option.
(Also, it might be easier just creating a pivot table in the front end with the Application field as a dimension as well and set it to horizontal.)
Anyway:
T1:
LOAD * INLINE [
CustomerNumber, Application
1, App1
1, App2
2, App1
2, App2
2, App3
3, App2
3, App3
4, App1
4, App2
4, App3
5, App1
5, App2
5, App3
6, App1
6, App3
];
Load
CustomerNumber,
if(index(AppString,'App1')>0,'Y') as App1,
if(index(AppString,'App2')>0,'Y') as App2,
if(index(AppString,'App3')>0,'Y') as App3;
Load
CustomerNumber,
Concat(Application, ';') as AppString
Resident T1
group by CustomerNumber;
Drop table T1;
Perhaps not as elegant as some solutions, Johannes, but it does provide me with what I was seeking.
This solution works just fine. 🙂
Much appreciated,
John
I'm glad Johannes' solution worked for you. It's nice and simple.
I wanted to make a note that the general solution to this problem is "Generic Load". Especially useful if you have a variable number of AppN values. See
http://qlikviewnotes.blogspot.com/2010/05/use-cases-for-generic-load.html
-Rob
Funny, while I've been searching for a solution and knew it (the solution I needed) was related to crosstables, I didn't realize it was the opposite of a crosstable! I will try this method too.
Thanks Rob!
--john