topic Re: Combine fields into one row in App Development

Combine fields into one row

New-Qlik — Wed, 20 Mar 2019 11:03:28 GMT

Hi,

I have a requirement to combine a columns data into on row as below example i.e UK and US country should come in same row for ID 3

Thanks in Advance


ID	Name	country
1	aa	Sweden
2	bb	Denmark
3	aa	US
3	aa	UK
4	cc	US
4	dd	UK


ID	Name	country
1	aa	Sweden
2	bb	Denmark
3	aa	US, UK
4	cc	US
4	dd	UK

Re: Combine fields into one row

PrashantSangle — Wed, 20 Mar 2019 11:19:47 GMT

try below

Temp:
Load ID,Concat(DISTINCT Name,',') as NewName, Concat(country,',') as newCountry
Group By ID;
Load * Inline [
ID, Name, country
1, aa, Sweden
2, bb, Denmark
3, aa, US
3, aa, UK
4, cc, US
4, dd, UK
];

Regards,
Prashant Sangle

Re: Combine fields into one row

tresB — Wed, 20 Mar 2019 11:19:57 GMT

Use concat()

Re: Combine fields into one row

raman_rastogi — Wed, 20 Mar 2019 11:28:56 GMT

Use Concat() Function

T1:
Load * Inline [
ID, Name, country
1, aa, Sweden
2, bb, Denmark
3, aa, US
3, aa, UK
4, cc, US
4, dd, UK];

Load ID,Name,Concat(country,',')as Country Resident T1 Group by ID, Name;
Drop Table T1;

Re: Combine fields into one row

Shubham_Deshmukh — Wed, 20 Mar 2019 12:04:39 GMT

Refer this thread ,

https://community.qlik.com/t5/New-to-Qlik-Sense/Comma-separated-values/m-p/1558374#M130193

Regards

Re: Combine fields into one row

New-Qlik — Wed, 20 Mar 2019 12:09:42 GMT

In my model country is coming from different table . joining them n doing concat is not a good idea.

Re: Combine fields into one row

New-Qlik — Wed, 20 Mar 2019 12:10:37 GMT

creating concat(country) as measure gives below result

ID	Name	country
total		Sweden,Denmark,US,UK
1	aa	Sweden
2	bb	Denmark
3	aa	US
3	aa	UK
4	cc	US
4	dd	UK

Re: Combine fields into one row

New-Qlik — Wed, 20 Mar 2019 12:13:20 GMT

Hi Shubham,

I didn't get what exactly your are doing for my issue.

Re: Combine fields into one row

Shubham_Deshmukh — Wed, 20 Mar 2019 12:33:10 GMT

You only want it for ID =3 ?
If not, just refer that whole thread, you will get an idea. Same issue mentioned in that thread.