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

Concat multiple columns and exclude null values


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.

RowABCD
1BricksStoneRocksBricks; Stones; Rocks
2BricksRocksBricks; ; Rocks
3Stone; 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

1 Solution

Accepted Solutions
MarcoWedel

Hi Drew,

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

QlikCommunity_Thread_114888_Pic1.JPG.jpg

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

View solution in original post

6 Replies
MK_QSL
MVP
MVP

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;

Not applicable
Author

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

AJ

Anonymous
Not applicable
Author

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

)

MarcoWedel

Hi Drew,

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

QlikCommunity_Thread_114888_Pic1.JPG.jpg

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

Not applicable
Author

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

Drew

MarcoWedel

You're welcome.

Please mark your thread as answered then.

Regards

Marco