Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Concat multiple columns and exclude null values

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

6 Replies
MVP
MVP

Re: Concat multiple columns and exclude null values

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

Re: Concat multiple columns and exclude null values

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

mov
Esteemed Contributor III

Re: Concat multiple columns and exclude null values

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

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

Re: Concat multiple columns and exclude null values

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

You're welcome.

Please mark your thread as answered then.

Regards

Marco

Community Browser