Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Combine Columns into Single Column (+Transform?)

Hello!

I'm stuck with what I think is a concatenate and a replace  - but I was hoping there might be a useful/simple/better way of doing it.

I have four columns of data I want to turn into one column of data, as below:

Easy way to do it?

Alex

10 Replies
Anonymous
Not applicable
Author

I would try with Nested if like below

Load

ID,

If(Labour='Yes','Labour',

     IF(Conservative='Yes', 'Conservative',

          If(Green='Yes', 'Green',

               If(UKIP='Yes','UKIP',Null()))) as Party

Anonymous
Not applicable
Author

But may be there is a more elegant way to do it

Anonymous
Not applicable
Author

Maybe this:

load
ID,
pick(ID,'Labor','Conservative','Green','UKIP') as Party

Not applicable
Author

Hi Alex,

You want to use crosstable for this.

Have a look at this maybe http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable

hope that helps

Joe

sunny_talwar

Is this what you are wanting to do?

Used Cross Table Load as mentioned by JoeSimmons

Best,

S

Not applicable
Author

Yea looks about right to me

One thing to note Alex on that is, if you have real nulls rather than the empty strings like this example, you won't need to do the second conditional load, as the nulls wouldn't generate you rows in the crosstable.

Would just save on doing an extra load


Joe

sunny_talwar

Sorry but what would be an example of real null? An empty Excel cell does not correspond to a null?

Thanks in advance for clarifying.

Best,

S

Not applicable
Author

Sorry, missed you were using excel, thought it was inline load (which would give you empty string rather than nulls).

If you actually remove the second load from your example, you'll see that gives you the correct result already.

Nulls is a fairy big topic but basically the difference in this example is an empty string is still considered a real value (and is actually selectable in QV) and so the cross would be able to perform on it, whereas a real null would not(and isn't selectable) and be like trying to cross on something that just doesn't exist.

This is a good thread on nulls that's worth a read.

http://community.qlik.com/docs/DOC-3155

Joe

sunny_talwar

Awesome, got it. Thanks Joe,

Best,

S