topic Using NUM in set analysis in QlikView

Using NUM in set analysis

Mon, 06 Jun 2011 15:12:34 GMT

Hi everyone,

I am struggling with set analysis for a field which I need to check for a certain format. It has to be 10 characters long and be of a numeric value. So farI have this but its not working. Where am I going wrong?

count({<Master.formType={'*101*'},year={$(=max(year))},Patient.NHSNumber=(NUM(Patient.NHSNumber,'0000000000'))}>}Master.counter_master)

Using NUM in set analysis

pat_agen — Mon, 06 Jun 2011 15:43:32 GMT

hi,

here is one workaround - (there are sure to be others!)

Use a search string on your Patient.NHSNumber field like this

len(purgechar([Patient.NHSNumber],'0123456789')) =10

this will strip out the numeric characters and then test the length. So, in theory it will only keep the records having a 10 character numeric value.

in set analysis you will have to write it like this:

{<[Patient.NHSNumber],={"=len(purgechar([Patient.NHSNumber],'0123456789')) =10"}>}.

The NUM() function you used formats the field it doesn't tell if the field is or isn't a numeric value without additional code.

Using NUM in set analysis

erichshiino — Tue, 07 Jun 2011 01:32:37 GMT

You could create this condition as a flag on your script:

Load... (your fields, etc) ,

if ( len (Patient.NHSNumber) =10 and isnum( Patient.NHSNumber), 1,0) as patientFlag

Resident/From (as your original code)

Then, in set analysis, your condition will simply be:

count({<Master.formType={'*101*'},year={$(=max(year))},patientFlag={1}>}Master.counter_master)

Hope this helps,

Erich