Count the Number of No's in a Row

[haymarketpaul]

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

[sasiparupudi1 · Accepted solution]

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;

[jyothish8807]

Hi Try this:

Do a cross table:

sum(if(WildMatch(Data,'No'),1,0)) ! !

[haymarketpaul]

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]