Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am struggling with the following need within a Qlikview script:
I have an input table with 2 columns. Column1 has a ID (Pack ID), column 2 a text. During load I need to to dynamically create a table that:
- unique IDs
- concatenates the text (with "+" separator") if the Pack_ID is same.
- concatenated items need to be acendingly sorted.
I have the feeling I need to use Peek function, use variables and possibly a variable.
Input:
Pack ID | Content |
11 | Item C |
11 | Item B |
11 | Item A |
12 | Item D |
13 | Item D |
13 | Item B |
14 | Item F |
14 | Item B |
14 | Item D |
14 | Item E |
14 | Item A |
Output needed:
Pack ID | Content Concatenated |
11 | Item A+Item B+Item C |
12 | Item D |
13 | Item B+Item D |
14 | Item A+Item B+Item D+Item E+Item F |
Any input much appreciated.
Thanks
Claude
Use Concat
T1:
LOAD [Pack ID],
Content
FROM
[\\lnn63f1\u_t1487402003\sasidp\My Documents\Qlikview\Example1.xlsx]
(ooxml, embedded labels, table is Sheet1);
T2:
Load
[Pack ID],
Concat(Content,'+') as Value
Resident T1
Group By [Pack ID]
Order By
[Pack ID],Content
;
drop table T1;
updated the syntax
Example file attached
Use Concat
T1:
LOAD [Pack ID],
Content
FROM
[\\lnn63f1\u_t1487402003\sasidp\My Documents\Qlikview\Example1.xlsx]
(ooxml, embedded labels, table is Sheet1);
T2:
Load
[Pack ID],
Concat(Content,'+') as Value
Resident T1
Group By [Pack ID]
Order By
[Pack ID],Content
;
drop table T1;
updated the syntax
PFA
Thanks for the script.
Works as needed !
Hi,
PFA,
Solution.
Regards,
Pankaj
Please close this thread by marking a correct answer