Concat Text with orginal record order

[micheledenardi]

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 ?

[micheledenardi · Accepted solution]

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;

[lironbaram]

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;