topic Concat multiple text rows in QlikView

Concat multiple text rows

Anonymous — Thu, 05 Nov 2015 05:46:13 GMT

Hi,

I have a table of the structure

Number	Row	Text
1	1	Text1
2	1	Text1
2	2	Text2

an I would like to transform it into a table of the structure

Number	Text
1	Text1
2	Text1 Text2

So I would like to concatenate each row of text with the same dataset number (first column) in just one row.

Thanks for any suggestion

Michael

Re: Concat multiple text rows

tamilarasu — Thu, 05 Nov 2015 13:03:50 GMT

Hi Michael,

Try to load like

load

Number

Concat(Text,' ') as NewText

resident Data

Group by Number;

I assume that your data loaded under the table name Data.. so i have used resident load.. if not adapt tour code with concat(Field,' ') as NewFieldname.. Let me know.

Re: Concat multiple text rows

Mark_Little — Thu, 05 Nov 2015 13:11:18 GMT

Hi,

Like Tamil suggested, Just to expand a with a coded example.

Table1:

LOAD * INLINE [

Number, Row, Text

1, 1, Text1

2, 1, Text1

2, 2, Text2

];

NoConcatenate

Table2:

Load

Number,

Concat(Text,' ') as NewText

Resident Table1

Group by Number;

Drop Table Table1;

Re: Concat multiple text rows

Anonymous — Thu, 05 Nov 2015 13:14:54 GMT

try this one:

Test1:

load

Number,

Concat('Text'&''&Row) as Text

Resident Test

group by Number;

Hope this will help!!

Re: Concat multiple text rows

Anonymous — Thu, 05 Nov 2015 13:20:27 GMT

PFA..

Re: Concat multiple text rows

Anonymous — Thu, 05 Nov 2015 14:55:13 GMT

Hi,

Thanks for your response. With a little modification, I got what I needed:

load

ID,

Concat(Text,' ',Number) as NewText

resident Data

Group by ID;

Regards

Michael