Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
oanalung
Contributor III
Contributor III

Concatenate many rows from the same table

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; 

 

Labels (1)
6 Replies
amartinez35
Partner
Partner

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; 

 

oanalung
Contributor III
Contributor III
Author

Hi, 

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

any idea why? 

amartinez35
Partner
Partner

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

MarcoWedel

What about the Date column?

oanalung
Contributor III
Contributor III
Author

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! 

Tins1980
Contributor
Contributor

You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable