topic Re: Display the row values as comma separated column values. in QlikView

Display the row values as comma separated column values.

ishanbansal1204 — Fri, 06 Jul 2018 06:47:20 GMT

Hi Experts,

I have a table created in Qlik sense and it has 10 columns and there is a requirement to add another column which has multiple values.

Based on the ID which is one of the column , I have to add another column and the values should be comma separated. Is there a way I can solve this by the formula in expression?

Current

ID	TID
1	123
1	456
2	963
2	258
2	741
3	789
3	654
3	123
3	951

Expected

ID	TID
1	123,456
2	963,258,741
3	789,654,123,951

I used the below query in dimension expression

=concat(TID,',')

But its giving me an error saying invalid dimension.

Thanks,

Ishan

Re: Display the row values as comma separated column values.

tresB — Fri, 06 Jul 2018 07:14:56 GMT

Possibly there is a way. And that could be suggested, if you could explain your requirement in bit more details; may be with an example with sample data.

Re: Display the row values as comma separated column values.

tresB — Fri, 06 Jul 2018 07:25:37 GMT

What if you try using like: = ID & ' , ' & TID

Re: Display the row values as comma separated column values.

Anonymous — Fri, 06 Jul 2018 07:51:24 GMT

Hello Ishan,

Use the below expression as calculated dimension.

AGGR(Concat(DISTINCT TID,','),ID)

Re: Display the row values as comma separated column values.

tresB — Fri, 06 Jul 2018 07:54:56 GMT

Now it's clear. Try:

Straight table:

Dim: ID

Exp: Concat( Distinct TID, ',')

If you are using as calculated dimension, try like:

=Aggr(Concat(Distinct TID, ','), ID)

Re: Display the row values as comma separated column values.

ishanbansal1204 — Fri, 06 Jul 2018 08:07:31 GMT

Thank you very Much. This is what I was looking for.

Re: Display the row values as comma separated column values.

ishanbansal1204 — Fri, 06 Jul 2018 08:11:45 GMT

In your expression you have grouped by ID. If we have 2 values to group what will be the expression?

Re: Display the row values as comma separated column values.

tresB — Fri, 06 Jul 2018 08:49:58 GMT

Check aggr() on help. It would be something like: Aggr( Concat(....), Dim1, Dim2)