topic Simple Selective Aggregation in QlikView

Simple Selective Aggregation

Roop — Wed, 07 Sep 2011 12:23:18 GMT

I have what I thought was a simple query in that I wish to return all those staff who are utilised part time during any 1 week.

In a table I need to return all those people who have between 1 and 4 available days during a standard week.

Available data:

RES_CODE Work_Type Days

AAAAAAAA WorkDays 5

AAAAAAAA Holiday 2

AAAAAAAA FirmBook 3

AAAAAAAA Available 0

BBBBBBBB WorkDays 5

BBBBBBBB Available 5

CCCCCCC WorkDays 5

CCCCCCC Holiday 3

CCCCCCC Available 2

DDDDDDD WorkDays 5

DDDDDDD Holiday 1

DDDDDDD FirmBook 1

DDDDDDD Available 3

Available = WorkDays - Holiday - Firm

Therefore from the above set, I would want to return 2 (as both CCCCCCC and DDDDDDD fulfill the crtieria required of Available greter than 0 and less than 5). Obviously the set is a lot larger than the above.

Any help would be gratefully received!

All of the work types are from different data sources .......

Simple Selective Aggregation

lironbaram — Wed, 07 Sep 2011 12:36:14 GMT

count({<Work_Type={Available},Days={">=1 <=4"}>} Distinct RES_CODE)

Simple Selective Aggregation

Roop — Wed, 07 Sep 2011 12:44:22 GMT

A great speedy response ... ! I should have said that the line Available does not exist as all the other values are from separate sources. Sorry, I should have made myself clearer !!!!!

Simple Selective Aggregation

lironbaram — Wed, 07 Sep 2011 12:58:13 GMT

sum((aggr(if(sum(Days)>=1 and sum(Days)<=4,1,0), RES_CODE))

Simple Selective Aggregation

Roop — Wed, 07 Sep 2011 13:30:07 GMT

Thank you very much - My rule is much more complex, but that part now works !!!