count distinct instances

[#1]

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

A

[alexandros17 · Accepted solution]

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;

[Gysbert_Wassenaar]

See attached qvw

[rwunderlich]

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

[#5]

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

A