topic Aggregation in Concat() in QlikView

Aggregation in Concat()

Sun, 11 Sep 2011 19:16:03 GMT

Hi All,

I'm trying to build a pivot table that will show in one column aggregation result on one dim split by another dim like in the below example. I tried Concat(Type, Count(Date)) but it seems Concat() does not accept Count().

Input

-------

Name, Type, Date

John, A, 2011-08-11

John, B, 2011-08-11

John, B, 2011-08-12

David, B, 2011-07-29

Result

---------

Name, Value

John, A1, B2

David, B1

Regards,

Przemek

Aggregation in Concat()

swuehl — Sun, 11 Sep 2011 21:10:32 GMT

Hi,

concat takes up to three parameter:

1. expression (i.e. the values to concatenate, often used with a field name)

2. delimiter

3. sort weight

Your second parameter Count(Date) is not a valid delimiter.

Maybe try

concat( aggr(Type&Count(Date),Type) )

instead.

Regards,

Stefan

Aggregation in Concat()

Anonymous — Mon, 12 Sep 2011 01:07:28 GMT

Przemek,
First, both concat() and count() are aggregation functions, so you can't use one within the other witjhout aggr().
Next, "one dim split by another dim" is apparently not what you really want, judging by the example of the desired result. The delimeter is comma.
I think that the dimension is Name, and the expression is something close to what Stefan suggests:
concat(aggr(Type&count(Date),Type),', ')

Aggregation in Concat()

Mon, 12 Sep 2011 07:37:26 GMT

Thank you. I've checked the formula against the example and I got:

John A1, B3

David -

Re: Aggregation in Concat()

gandalfgray — Mon, 12 Sep 2011 07:57:24 GMT

Hi przemek

If you want to create a pivot chart with Name verticaly and Value horizontaly

you just need to group by Name,Type - no need to use concat:

Result:

Load Name,

Type&Count(Date) As Value

Resident Input

Group By Name,Type;

Here's the pivot chart you get:

hth/gg

Re: Aggregation in Concat()

Mon, 12 Sep 2011 08:05:26 GMT

Yes, though the data model requires that to put it in chart formula.

Re: Aggregation in Concat()

gandalfgray — Mon, 12 Sep 2011 08:13:56 GMT

I'm not sure I understand what you want, but you can try this then:

_Result:

Load Name,

Type&Count(Date) As _Value

Resident Input

Group By Name,Type;

Result:

Load Name,

Concat(_Value,', ') As Value

Resident _Result

Group By Name;

Drop Table _Result;

hth/gg

Re: Aggregation in Concat()

swuehl — Mon, 12 Sep 2011 08:18:10 GMT

I see.

Try a

=concat(aggr(Type&count(Date),Type,Name),', ')

(adding the Name to the aggr() dimensions).

Regards,

Stefan

Re: Aggregation in Concat()

Mon, 12 Sep 2011 10:17:15 GMT

This is it! Thank you, Stefan.