Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
snowman
Contributor III
Contributor III

Count value across columns

Hi there,

 

 I have a simple table with millions of rows, and the columns are: Date, ID, T1, T2, T3, T4, T5, T6.

ID has values from either 1 to 10; T1 to T6 have values of either 'R', 'Y', or 'G'

For example, a record could look like: 5/15/2020 | 3 | 'R' | | 'G' | | 'G' | | 'Y' | | 'G' | | 'R' | 

I try to create a new column which counts the total number of 'R' from column T1 to T6, so that in the above example, it will give 2 for that record.  Is there a function to count across specific columns of a row ? 

 

Thanks

Labels (1)
2 Solutions

Accepted Solutions
Taoufiq_Zarra
Master II
Master II

@snowman  for example I can use :

SubStringCount(T1&T2&T3&T4&T5&T6, 'R') as Count_of_R

for example :

load *,SubStringCount(T1&T2&T3&T4&T5&T6, 'R') as Count_of_R inline [
Date, ID, T1, T2, T3, T4, T5, T6
5/15/2020,3,'R','G','G','Y','G','R'
5/15/2020,3,'R','G','G','Y','G','R'
];

 

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") 😉

View solution in original post

snowman
Contributor III
Contributor III
Author

Thanks a lot, and it works. Not aware of SubStringCount(), and save me my stupid work on multiple IF loops.

View solution in original post

2 Replies
Taoufiq_Zarra
Master II
Master II

@snowman  for example I can use :

SubStringCount(T1&T2&T3&T4&T5&T6, 'R') as Count_of_R

for example :

load *,SubStringCount(T1&T2&T3&T4&T5&T6, 'R') as Count_of_R inline [
Date, ID, T1, T2, T3, T4, T5, T6
5/15/2020,3,'R','G','G','Y','G','R'
5/15/2020,3,'R','G','G','Y','G','R'
];

 

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") 😉

View solution in original post

snowman
Contributor III
Contributor III
Author

Thanks a lot, and it works. Not aware of SubStringCount(), and save me my stupid work on multiple IF loops.

View solution in original post