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: 
micheledenardi
Specialist II
Specialist II

Concat Text with orginal record order

Good morning,

during the load script i would like to concat texts grouping by KEY1,KEY2 and KEY3 respecting the original load order.

My source file is this:

2018-10-16 09_11_54-SampleData.xlsx - Excel.png

I would like to obtain 2 rows with the concatenation of texts using the same order of the original file:

2018-10-16 09_19_40-SampleData.xlsx - Excel.png

By usign below script i get a wrong concat order:

TEMP:

LOAD

     KEY1,

     KEY2,

     KEY3,

     Concat(Text) as FullText

FROM

(ooxml, embedded labels, table is Foglio1)

group by KEY1,KEY2,KEY3;

And the wrong result is:

2018-10-16 09_22_53-QlikView x64 - Copia del rivenditore - [C__Users_denardm1_Documents_QV1.qvw_].png

How to achieve this ?

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
1 Solution

Accepted Solutions
micheledenardi
Specialist II
Specialist II
Author

TEMP:

LOAD

RecNo() as record,

KEY1,

    KEY2,

    KEY3,

    Text

FROM

(ooxml, embedded labels, table is Foglio1);

SampleData:

NoConcatenate

Load

KEY1,

    KEY2,

    KEY3,

    Concat(Text,'',record) as FullText

Resident TEMP

group by KEY1,KEY2,KEY3

order by record;

Drop Table TEMP;

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

2 Replies
micheledenardi
Specialist II
Specialist II
Author

TEMP:

LOAD

RecNo() as record,

KEY1,

    KEY2,

    KEY3,

    Text

FROM

(ooxml, embedded labels, table is Foglio1);

SampleData:

NoConcatenate

Load

KEY1,

    KEY2,

    KEY3,

    Concat(Text,'',record) as FullText

Resident TEMP

group by KEY1,KEY2,KEY3

order by record;

Drop Table TEMP;

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
lironbaram
Partner - Master III
Partner - Master III

hi

this will give the desired results

Temp:

LOAD KEY1,

     KEY2,

     KEY3,

     Text,

     RowNo() as rowNum


    

FROM

(ooxml, embedded labels, table is Foglio1)

group by KEY1,KEY2,KEY3;

exit SCRIPT


Final:

LOAD KEY1,

     KEY2,

     KEY3,

     Concat(Text,'',rowNum) as FullText

Resident Temp

group by KEY1,KEY2,KEY3

order by KEY3,rowNum;

Drop table Temp;