Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jwaligora
Creator II
Creator II

Count number of occurrences per row, across multiple columns.

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.

 

1 Solution

Accepted Solutions
Saravanan_Desingh

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
];

commQV34.PNG

View solution in original post

4 Replies
Taoufiq_Zarra

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 :

 

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Saravanan_Desingh

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
];

commQV34.PNG

jwaligora
Creator II
Creator II
Author

@Taoufiq_Zarra , @Saravanan_Desingh 

Thank you both! Prompt and right on the money.

Saravanan_Desingh

you welcome