topic Re: Concatenate many rows from the same table in QlikView

Concatenate many rows from the same table

oanalung — Wed, 23 Feb 2022 14:23:37 GMT

Hi,

I want to concatenate many rows into one based on two columns having the same value.

All the data is in the same table.

Example

User_Id Description Flag Date

1 Text1 0 2020-01-01

1 Text2 0 2020-01-01

1 Text3 1 2020-01-01

I want to achieve:

User ID Description Flag Date

1 Text1, Text2 0 2020-01-01

1 Text3 1 2020-01-01

based on user_id being the same and same value in column flag.

as script I have:

LOAD

user_id as [User ID],

description as [Description],

flag as [Flag]

date as [Date];

SQL

Select

user_id,

description,

flag,

date from table_name;

Re: Concatenate many rows from the same table

Aurelien_Martinez — Wed, 23 Feb 2022 15:22:26 GMT

Hi,

You can try with the concat function

LOAD

user_id as [User ID],

Concat(description, ', ') as [Description],

flag as [Flag]

date as [Date]

Group By

user_id ,

flag ,

date

;

SQL

Select

user_id,

description,

flag,

date from table_name;

Re: Concatenate many rows from the same table

oanalung — Wed, 23 Feb 2022 15:32:18 GMT

Hi,

thank you very much, I have tried this and it looks like it's not running, I get invalid expression.

any idea why?

Re: Concatenate many rows from the same table

Aurelien_Martinez — Wed, 23 Feb 2022 15:39:21 GMT

Perhaps a missing comma after the line "flag as [Flag]"

Re: Concatenate many rows from the same table

MarcoWedel — Wed, 23 Feb 2022 19:45:02 GMT

What about the Date column?

Re: Concatenate many rows from the same table

oanalung — Thu, 24 Feb 2022 16:24:39 GMT

Yes, this is working, thank you very much.

Is there another way to achieve this in the script without using the group by?

Just concatenating everything if the User_id column has the same value.

thank you!