Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Sample Data Set:
ID, ColourA, ColourB, ColourC, Description, ...Result
1, Blue, Green, Blue, Circle, ...2
2, Yellow, Blue, Black, Square, ...1
3, Green, Teal, Black, Triangle, ...0
4, Orange, Blue, Blue, Blue, ...2
I'm looking for the most effective way (in QV script) of adding a column that will count the number of times that 'Blue' appears per row across the 3 colour columns (ColourA, ColourB, ColourC). Desired outcome is represented above by the "Result" column.
Thanks,
J.
One solution is.
tab1:
LOAD *, -RangeSum(ColourA='Blue',ColourB='Blue',ColourC='Blue') As Result;
LOAD * INLINE [
ID, ColourA, ColourB, ColourC, Description
1, Blue, Green, Blue, Circle
2, Yellow, Blue, Black, Square
3, Green, Teal, Black, Triangle
4, Orange, Blue, Blue, Blue
];
if I can propose a quick suggestions, maye be :
Data:
load A+B+C as Result,*;
load if(ColourA='Blue',1,0) as A,if(ColourB='Blue',1,0) as B,if(ColourC='Blue',1,0) as C,* inline [
ID, ColourA, ColourB, ColourC, Description
1, Blue, Green, Blue, Circle
2, Yellow, Blue, Black, Square
3, Green, Teal, Black, Triangle
4, Orange, Blue, Blue, Blue
];
drop fields A,B,C;
output :
One solution is.
tab1:
LOAD *, -RangeSum(ColourA='Blue',ColourB='Blue',ColourC='Blue') As Result;
LOAD * INLINE [
ID, ColourA, ColourB, ColourC, Description
1, Blue, Green, Blue, Circle
2, Yellow, Blue, Black, Square
3, Green, Teal, Black, Triangle
4, Orange, Blue, Blue, Blue
];
@Taoufiq_Zarra , @Saravanan_Desingh
Thank you both! Prompt and right on the money.
you welcome