topic Re: Concat multiple columns and exclude null values in QlikView

Concat multiple columns and exclude null values

Fri, 18 Apr 2014 19:53:27 GMT

Hi,

I have a table with multiple columns that a want to concatenate into a single column and separate the values with a semicolon. For example, column A has "Bricks" column B has "Stones" column C has "Rocks." I want to create a column D that reads "Bricks; Stones; Rocks." This is easy to do if every row has a value in A, B, and C, but that is not the case with my data set.

Row	A	B	C	D
1	Bricks	Stone	Rocks	Bricks; Stones; Rocks
2	Bricks	Rocks		Bricks; ; Rocks
3	Stone			; Stone; ;

I want row 2 to read "Bricks; Rocks" and not "Bricks; ; Rocks", and I want row 3 column D to read "Stone" and not "; Stone; ; ".

This is simple to do in Access, but I cannot figure it out in QlikView. Any help would be appreciated!

Thanks, Drew

Re: Concat multiple columns and exclude null values

MK_QSL — Fri, 18 Apr 2014 20:11:38 GMT

Load

IF(Right(D1,1)=';',Left(D1,Len(D1)-1),D1) as D;

Load *, IF(Len(Trim(A))<>0,A&';','')&IF(Len(Trim(B))<>0,B&';','')&IF(Len(Trim(C))<>0,C,'') as D1 Inline

[

Row, A, B, C

1, Bricks, Stone, Rocks

2, Bricks, Rocks,

3, Stone,

];

Drop Field D1;

Re: Concat multiple columns and exclude null values

Fri, 18 Apr 2014 20:12:56 GMT

Hey Drew,

Maybe like attached.

I basically brought all the three fields into one field and used concat() function on the front-end. concat will take care of null values.

Thanks

Re: Concat multiple columns and exclude null values

Anonymous — Fri, 18 Apr 2014 20:19:33 GMT

I know it is not elegant , but should work

=mid(

if(len(trim(A)), A & ';','') & if(len(trim(B)), B & ';','') & if(len(trim(C)), C & ';',''),1,

len(if(len(trim(A)), A & ';','') & if(len(trim(B)), B & ';','') & if(len(trim(C)), C & ';',''))-1

)

Re: Concat multiple columns and exclude null values

MarcoWedel — Fri, 18 Apr 2014 22:19:35 GMT

Hi Drew,

if you prefer doing this on the script side, then here's another solution:

tabInput:
CrossTable (attr, data, 4)
LOAD Row, 
    A, 
    B, 
    C,
    A,
    B,
    C
FROM [http://community.qlik.com/thread/114888]
(html, codepage is 1252, embedded labels, table is @1);

Left Join (tabInput)
LOAD Distinct
    Row,
    Concat(data, ';') as D
Resident tabInput
Where Len(data)>0
Group By Row;

DROP Fields attr, data;

hope this helps

regards

Marco

Re: Concat multiple columns and exclude null values

Mon, 21 Apr 2014 13:32:05 GMT

I did not try any of the solutions aside from Marco's, which worked perfectly! Thanks Marco!

Drew

Re: Concat multiple columns and exclude null values

MarcoWedel — Mon, 21 Apr 2014 20:49:23 GMT

You're welcome.

Please mark your thread as answered then.

Regards

Marco