topic Re: count distinct instances in QlikView

count distinct instances

Thu, 18 Jul 2013 13:30:38 GMT

Product	Colour
Apple	Red
Apple	Red
Apple	Green
Truck	Red
Truck	Red
Car	Blue
Bike	Red
Bike	Green

Hi Experts

I use source data from an excel worksheet - which looks a little like the above.

I am only looking to count each instance when a product is ONLY available in RED - so the above answer would be 2 (for each of the red trucks) - apples /cars and bikes are available in other colours so should not be counted.

Ideally, I'd like to be able to put the identifier in the load so I can easilly build charts based on that rule - but any guidence would be greatly appreciated

Thanks

Re: count distinct instances

alexandros17 — Thu, 18 Jul 2013 13:53:29 GMT

AAA:

LOAD * Inline [

Product, Colour

Apple, Red

Apple, Red

Apple, Green

Truck, Red

Truck, Red

Car, Blue

Bike, Red

Bike, Green

];

Left Join

LOAD

Product,

Count( distinct Colour) as diff

Resident AAA

Group by Product;

Left Join

LOAD

Count(Product) as numInst,

Product,

Colour

Resident AAA

Group by Product, Colour;

CCC:

NoConcatenate

LOAD

Product,

Colour,

If(Colour = 'Red' and diff = 1, numInst, 0) as myNum,

If(Colour = 'Red' and diff = 1, 1, 0) as myFlag

Resident AAA;

DROP Table AAA;

Re: count distinct instances

Gysbert_Wassenaar — Thu, 18 Jul 2013 13:54:15 GMT

See attached qvw

Re: count distinct instances

rwunderlich — Thu, 18 Jul 2013 14:28:26 GMT

A slight modification to Gysbert's solution, because I understand you want the counter only for Red Only products.

See attached.

JOIN

LOAD

Product, 1 as RedOnlyCounter

WHERE Colors='Red'

;

LOAD Product, concat(DISTINCT Colour) as Colors

Resident T1

group by Product;

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: count distinct instances

Thu, 18 Jul 2013 15:23:13 GMT

Hi Guys

Im afraid I can not open any of the sample QVW attached - im sure its to do with my license !!!

I managed to get alexandros17 solution to work for me

Thanks for all your time