topic Aggregation problem in QlikView

Aggregation problem

sifatnabil — Fri, 10 Oct 2014 12:20:34 GMT

Hi,

I have a table structure like this:

PrimaryKey	TradeID	Electronic	Voice	Volume
123	11	(blank)	X	10
124	11	X	(blank)	5
125	11	(blank)	X	10

What I want do is:

For each unique TradeID,

if Electronic contains X in any row AND if Voice contains X in any row,

count(distinct TradeID) and

sum(Volume)

I know this involves some sort of aggregation and I tried this in a textbox:

count(distinct if(len(Electronic)>0 and len(Voice)>0, TradeID))

But this returns an error.

Re: Aggregation problem

Gysbert_Wassenaar — Fri, 10 Oct 2014 12:46:18 GMT

count({<TradeID=p({<Electronic={'X'}>}TradeID)*p({<Voice={'X'}>}TradeID)>} DISTINCT TradeID)

sum({<TradeID=p({<Electronic={'X'}>}TradeID)*p({<Voice={'X'}>}TradeID)>} Volume)

Re: Aggregation problem

JonnyPoole — Fri, 10 Oct 2014 12:49:56 GMT

Try something like this:

It checks if 'X' is in any row of Electronic and any row of Voice for each TradeID.

If its in both at least one time, then count the tradeID otherwise don't count the tradeId

=count( DISTINCT

if(

aggr(SubStringCount(Concat(DISTINCT Electronic),'X'),TradeID)

and

aggr(SubStringCount(Concat(DISTINCT Voice),'X'),TradeID)

, TradeID)

)

Re: Aggregation problem

sifatnabil — Fri, 10 Oct 2014 12:58:48 GMT

Thanks Gysbert, sorry I should actually correct this line in my question:

if Electronic contains X in any row AND if Voice contains X in any row,

if Electronic is not blank in some rows AND if Voice is not blank in some rows

That's why I used concat(len(Electronic)>0 ..

Could you incorporate len into your solution?

Re: Aggregation problem

Gysbert_Wassenaar — Fri, 10 Oct 2014 13:01:26 GMT

Ah, that's a lot easier. count({<Electronic={'X'},Voice={'X'}>} distinct TradeID)

Re: Aggregation problem

sifatnabil — Fri, 10 Oct 2014 13:04:22 GMT

Sorry, the reason I can't select X is because X can be any string, like ABC or DVD, how can I amend it like count({<Electronic={'Any value except blank'},Voice={'Any value except blank'}>} distinct TradeID) ?

Re: Aggregation problem

Gysbert_Wassenaar — Fri, 10 Oct 2014 13:10:58 GMT

Use '*' instead of 'X'.

Re: Aggregation problem

sifatnabil — Fri, 10 Oct 2014 13:28:30 GMT

Thanks, actually your original answer worked but with '*'. Thanks! And what if I wanted the inverse? i.e. can I use count({<TradeID=p({<Electronic-={'*'}>}TradeID)*p({<Voice-={'*'}>}TradeID)>} DISTINCT TradeID) ?

Set analysis doesn't seem to allow -=

Also, what's the best way to display this in chart format if I want the dimension to be date or any other field?

Re: Aggregation problem

sifatnabil — Fri, 10 Oct 2014 13:49:57 GMT

bump.