Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
QV12 SR4
I can probably do this in a long winded way somehow but can't help thinking there may be an easier approach.
I need to count the number of times the word 'No' appears in each row of a table.
For example...
UserID | OptIn1 | OptIn2 | OptIn3 | OptIn4 | OptIn5 | Count of No's |
---|---|---|---|---|---|---|
1 | No | No | No | 3 | ||
2 | No | No | 2 | |||
3 | No | No | No | No | 4 | |
4 | 0 | |||||
5 | No | 1 |
There are actually around 100 of these OptIn fields that can either be blank or have a 'No' in them so i need to count the total number of No's for each row across around 100 fields.
My current thinking is to concatenate the fields together and do a SubStringCount on the word No
Any ideas appreciated
t1:
LOAD * INLINE [
UserID,OptIn1,OptIn2,OptIn3,OptIn4,OptIn5
1,No,,No,No,
2,,No,,,No
3,No,No,,No,No
4,,,,,
5,,,No,,
];
t2:
CrossTable(Data,Value,1)
Load
UserID,OptIn1,OptIn2,OptIn3,OptIn4,OptIn5
Resident t1
;
Left Join(t1)
Load
UserID,
Count(Data) as [Count of No's]
Resident t2
where Value='No'
Group By UserID
;
drop table t2;
t1:
LOAD * INLINE [
UserID,OptIn1,OptIn2,OptIn3,OptIn4,OptIn5
1,No,,No,No,
2,,No,,,No
3,No,No,,No,No
4,,,,,
5,,,No,,
];
t2:
CrossTable(Data,Value,1)
Load
UserID,OptIn1,OptIn2,OptIn3,OptIn4,OptIn5
Resident t1
;
Left Join(t1)
Load
UserID,
Count(Data) as [Count of No's]
Resident t2
where Value='No'
Group By UserID
;
drop table t2;
Hi Try this:
Do a cross table:
sum(if(WildMatch(Data,'No'),1,0)) ! !
Thanks that works nicely.
My SubStringCount effort below seems to work a bit faster as your t2 table gets very large with my data volumes but your solution looks more elegant.
SubStringCount(BR_Ad_Promo_Opt_In & BR_Awards_Opt_In & BR_Brand_Opt_In & BR_Circ_Opt_In & BR_Conferences_Opt_In & BR_Exhibitions_Opt_In & BR_Forums_Opt_In &
BR_Medical_Opt_In & BR_Online_Events_Opt_In & BR_Recruit_Promo_Opt_In & BR_Recruitment_Opt_In & BR_Service_Message_Opt_In & BR_Subs_Opt_In &
BR_Surveys_Opt_In & BR_Third_Party_Opt_In & CAM_Ad_Promo_Opt_In & CAM_Awards_Opt_In & CAM_Brand_Opt_In & CAM_Circ_Opt_In & CAM_Conferences_Opt_In &
CAM_Exhibitions_Opt_In & CAM_Forums_Opt_In & CAM_JobsOptIn & CAM_Medical_Opt_In & CAM_Online_Events_Opt_In & CAM_Recruit_Promo_Opt_In &
CAM_Recruitment_Opt_In & CAM_Service_Message_Opt_In & CAM_Subs_Opt_In & CAM_Surveys_Opt_In & CAM_Third_Party_Opt_In & MED_Ad_Promo_Opt_In &
MED_Awards_Opt_In & MED_Brand_Opt_In & MED_Circ_Opt_In & MED_Conferences_Opt_In & MED_Exhibitions_Opt_In & MED_Forums_Opt_In & MED_Medical_Opt_In &
MED_Online_Events_Opt_In & MED_Recruit_Promo_Opt_In & MED_Recruitment_Opt_In & MED_Subs_Opt_In & MED_Surveys_Opt_In & MED_Third_Party_Opt_In &
MKT_Ad_Promo_Opt_In & MKT_Awards_Opt_In & MKT_Brand_Opt_In & MKT_Circ_Opt_In & MKT_Conferences_Opt_In & MKT_Exhibitions_Opt_In & MKT_Forums_Opt_In &
MKT_Medical_Opt_In & MKT_Online_Events_Opt_In & MKT_Recruit_Promo_Opt_In & MKT_Recruitment_Opt_In & MKT_Service_Message_Opt_In & MKT_Subs_Opt_In &
MKT_Surveys_Opt_In & MKT_Third_Party_Opt_In & MWK_Ad_Promo_Opt_In & MWK_Awards_Opt_In & MWK_Brand_Opt_In & MWK_Circ_Opt_In & MWK_Conferences_Opt_In &
MWK_Exhibitions_Opt_In & MWK_Forums_Opt_In & MWK_Medical_Opt_In & MWK_Online_Events_Opt_In & MWK_Recruit_Promo_Opt_In & MWK_Recruitment_Opt_In &
MWK_Service_Message_Opt_In & MWK_Subs_Opt_In & MWK_Surveys_Opt_In & MWK_Third_Party_Opt_In & PRW_Ad_Promo_Opt_In & PRW_Awards_Opt_In & PRW_Brand_Opt_In &
PRW_Circ_Opt_In & PRW_Conferences_Opt_In & PRW_Exhibitions_Opt_In & PRW_Forums_Opt_In & PRW_JobsOptIn & PRW_Medical_Opt_In & PRW_Online_Events_Opt_In &
PRW_Recruit_Promo_Opt_In & PRW_Recruitment_Opt_In & PRW_Service_Message_Opt_In & PRW_Subs_Opt_In & PRW_Surveys_Opt_In & PRW_Third_Party_Opt_In,'No')
as [Count of No's]