Split Field into Multiple Fields

[johnca]

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

[Anonymous · Accepted solution]

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;

[johnca]

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

[rwunderlich]

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

[johnca]

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