Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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
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
)
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
I did not try any of the solutions aside from Marco's, which worked perfectly! Thanks Marco!
Drew
You're welcome.
Please mark your thread as answered then.
Regards
Marco