Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

johncaqc
Valued Contributor

Split Field into Multiple Fields

I have data that looks like this:

Customer Apps.png

And need to make it look like this:

Customer Apps - Desired.png

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

1 Solution

Accepted Solutions
jsn
Honored Contributor

Re: Split Field into Multiple Fields

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;

4 Replies
jsn
Honored Contributor

Re: Split Field into Multiple Fields

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;

johncaqc
Valued Contributor

Re: Split Field into Multiple Fields

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

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

johncaqc
Valued Contributor

Re: Split Field into Multiple Fields

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

Community Browser