topic Re: Concat Text with orginal record order in QlikView

Concat Text with orginal record order

micheledenardi — Tue, 16 Oct 2018 07:25:10 GMT

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:

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

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:

How to achieve this ?

Re: Concat Text with orginal record order

micheledenardi — Tue, 16 Oct 2018 07:33:49 GMT

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;

Re: Concat Text with orginal record order

lironbaram — Tue, 16 Oct 2018 07:45:59 GMT

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;