topic Split Field into Multiple Fields in QlikView

Split Field into Multiple Fields

johnca — Tue, 18 Dec 2012 02:52:22 GMT

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

Re: Split Field into Multiple Fields

Anonymous — Tue, 18 Dec 2012 03:11:36 GMT

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;

Re: Split Field into Multiple Fields

johnca — Tue, 18 Dec 2012 03:58:43 GMT

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

Re: Split Field into Multiple Fields

rwunderlich — Tue, 18 Dec 2012 05:55:49 GMT

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

Re: Split Field into Multiple Fields

johnca — Tue, 18 Dec 2012 13:51:56 GMT

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