Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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:
Thanks a lot, and it works. Not aware of SubStringCount(), and save me my stupid work on multiple IF loops.
@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:
Thanks a lot, and it works. Not aware of SubStringCount(), and save me my stupid work on multiple IF loops.