topic Concatenate the values of multiple records for one field in QlikView

Concatenate the values of multiple records for one field

Fri, 18 Mar 2011 22:23:42 GMT

Is it possible to concatenate rows in one field in this way:

Let's say we have this table:

Client Product

1 A

1 B

2 B

2 C

2 D

I would like to generate the following table:

Client Products

1 A - B

2 B - C - D

The amount of products per client varies.

Thanks for your help

Johan

Concatenate the values of multiple records for one field

Miguel_Angel_Baeyens — Sat, 19 Mar 2011 00:05:18 GMT

Hello Johan,

I'm not very used to do aggregations in the script, but this seems a good moment to do something like this:

Table1:LOAD Client, ProductFROM Source; Table2:LOAD Client, If(Previous(Client) = Client, RangeSum(Peek('ProductsAcum'), -1 * Products), 1) AS ProductsAcumRESIDENT Table1ORDER BY Client; // Table previously loaded with raw data

Hope that helps

Concatenate the values of multiple records for one field

maneshkhottcpl — Sat, 19 Mar 2011 03:34:38 GMT

Hi,

U can also use the previous tricks to concatenate the row values

Client Product

1 A

1 B

2 B

2 C

2 D

load Client,concat(Product,'-') as NewProduct resident Tablename group by Client;

Please refer attached application.

Concatenate the values of multiple records for one field

Anonymous — Sun, 20 Mar 2011 18:02:08 GMT

Johan,

It can be done by using concat() function:
concat(distinct Products, ' - ')
You can use it either in script or on front end, whatever you prefer.

Concatenate the values of multiple records for one field

Mon, 21 Mar 2011 07:56:15 GMT

Thanks a lot guys!

I like the simplicity of the concat() and group by combination!