topic Re: How to dynamically concatenate field values during load (Peek/while) in QlikView

How to dynamically concatenate field values during load (Peek/while)

claude_jakob — Mon, 09 Apr 2018 13:40:27 GMT

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

Re: How to dynamically concatenate field values during load (Peek/while)

claude_jakob — Mon, 09 Apr 2018 13:41:49 GMT

Example file attached

Re: How to dynamically concatenate field values during load (Peek/while)

sasiparupudi1 — Mon, 09 Apr 2018 13:47:45 GMT

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

Re: How to dynamically concatenate field values during load (Peek/while)

sasiparupudi1 — Mon, 09 Apr 2018 13:50:56 GMT

PFA

Re: How to dynamically concatenate field values during load (Peek/while)

claude_jakob — Mon, 09 Apr 2018 14:33:05 GMT

Thanks for the script.

Works as needed !

Re: How to dynamically concatenate field values during load (Peek/while)

passionate — Mon, 09 Apr 2018 15:00:23 GMT

Hi,

PFA,

Solution.

Regards,

Pankaj

Re: How to dynamically concatenate field values during load (Peek/while)

sasiparupudi1 — Mon, 09 Apr 2018 15:02:44 GMT

Please close this thread by marking a correct answer