topic Re: Concat string in QlikView

Concat string

Tue, 25 Nov 2014 16:00:43 GMT

Dear all,

I have a one table like

Product
1
2
3
4
5
6
7
8
9
10

and other table like

Product	Event
2	Gift
2	Xmas
3	Gift
5	Gift
7	Gift
7	Xmas
10	Gift
10	Xmas
10	New Year

Now I want in Staight table like below..

Product	Event
1	None
2	Gift, Xmas
3	Gift
4	None
5	Gift
6	None
7	Gift, Xmas
8	None
9	None
10	Gift, Xmas, New Year

Acually I want to concate all event against the Product in Straight Table.

Please help me.

Regards

Vinay

Re: Concat string

Tue, 25 Nov 2014 16:02:51 GMT

Hi Vinay,

Concat(Distinct Event,',')

give that a try in your expression with Product as your dimension

hope that helps

Joe

Re: Concat string

simondachstr — Tue, 25 Nov 2014 16:04:14 GMT

Load first table and left join the second table.

Then do a resident load with a group by

LOAD

Product,

if(isNull(Event),'None',Concat(Event,','))

Resident Table

Group by Product;

Edit:

Thought you wanted to achieve that in the script - for a straight table solution go with the above suggestion.

Re: Concat string

Tue, 25 Nov 2014 16:11:24 GMT

Hi,

i am using below in straight table expression

if(IsNull(Event),'None',Concat(Distinct Event,','))

but not give me coorect result.

Re: Concat string

Tue, 25 Nov 2014 16:16:49 GMT

Hi,

Just load the two tables with Field name 'Product' remaining same.

Attaching the document for your help.

Re: Concat string

Tue, 25 Nov 2014 16:19:29 GMT

An example app would be good if possible

Re: Concat string

rubenmarin — Tue, 25 Nov 2014 16:40:30 GMT

Hi, please check attachment, I provided a script solution and a expression solution.

Hope this helps.

Re: Re: Concat string

JonnyPoole — Tue, 25 Nov 2014 16:41:57 GMT

I would try putting the if() within the aggregation function so that it calculates at the detailed level and then puts together a delimitted list of the distinct values.

Concat(Distinct if(IsNull(Event),'None',Event) ,',')