topic Re: Combine Columns into Single Column (+Transform?) in QlikView

Combine Columns into Single Column (+Transform?)

Mon, 02 Feb 2015 13:01:17 GMT

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

Re: Combine Columns into Single Column (+Transform?)

Anonymous — Mon, 02 Feb 2015 13:09:01 GMT

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

Re: Combine Columns into Single Column (+Transform?)

Anonymous — Mon, 02 Feb 2015 13:09:18 GMT

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

Re: Combine Columns into Single Column (+Transform?)

Anonymous — Mon, 02 Feb 2015 13:24:59 GMT

Maybe this:

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

Re: Combine Columns into Single Column (+Transform?)

Mon, 02 Feb 2015 13:26:05 GMT

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

Re: Combine Columns into Single Column (+Transform?)

sunny_talwar — Mon, 02 Feb 2015 13:36:59 GMT

Is this what you are wanting to do?

Used Cross Table Load as mentioned by JoeSimmons

Best,

Re: Combine Columns into Single Column (+Transform?)

Mon, 02 Feb 2015 13:51:45 GMT

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

Re: Combine Columns into Single Column (+Transform?)

sunny_talwar — Mon, 02 Feb 2015 13:54:41 GMT

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,

Re: Combine Columns into Single Column (+Transform?)

Mon, 02 Feb 2015 14:21:42 GMT

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

Re: Combine Columns into Single Column (+Transform?)

sunny_talwar — Mon, 02 Feb 2015 14:25:30 GMT

Awesome, got it. Thanks Joe,

Best,

Re: Combine Columns into Single Column (+Transform?)

maxgro — Mon, 02 Feb 2015 14:49:53 GMT

tt:

load * inline [

ID, Labour,Conservative,Green,UKIP

1,Yes,,,

2,,Yes,,

3,,,Yes,

4,,,,Yes

];

ct:

CrossTable (Party, YesNo)

LOAD * Resident tt

;

final:

NoConcatenate load ID, Party Resident ct where len(trim(YesNo))>0;

DROP Table ct, tt;