Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
But may be there is a more elegant way to do it
Maybe this:
load
ID,
pick(ID,'Labor','Conservative','Green','UKIP') as Party
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
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
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
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
Awesome, got it. Thanks Joe,
Best,
S