topic Expressions - Ignore rows with missing field values in QlikView

Expressions - Ignore rows with missing field values

Tue, 02 Dec 2014 19:57:59 GMT

Hi everyone,

I have a simple expression to calculate product age: (Year - [First Year Delivery]). How do I tell the expression to ignore rows where [First Year Delivery] is blank? Those rows are creating results that distort my later calculations of averages.

Re: Expressions - Ignore rows with missing field values

MarcoWedel — Tue, 02 Dec 2014 20:28:42 GMT

in the script:

Where not IsNull([First Year Delivery])

Where Len([First Year Delivery]) > 0

Where [First Year Delivery] <> ''

Re: Expressions - Ignore rows with missing field values

Tue, 02 Dec 2014 22:04:15 GMT

Hi Marco,

Would you mind showing me how to insert in script? I have created buckets for my data with the following - where would I add the WHERE qualifier?

if([First Year Delivery]<0,0,

if((FiscalYear-[First Year Delivery])<0,0,

if((FiscalYear-[First Year Delivery])>0 and (FiscalYear-[First Year Delivery])<10,'0-9',

if((FiscalYear-[First Year Delivery])>=10 and (FiscalYear-[First Year Delivery])<20,'10-19',

if((FiscalYear-[First Year Delivery])>=20 and (FiscalYear-[First Year Delivery])<30,'20-29',

if((FiscalYear-[First Year Delivery])>=30,'30+',

)))))) as [Age Bucket]

Re: Expressions - Ignore rows with missing field values

MarcoWedel — Tue, 02 Dec 2014 22:16:39 GMT

Hi,

something like

LOAD FiscalYear,

[First Year Delivery],

if([First Year Delivery]<0,0,

if((FiscalYear-[First Year Delivery])<0,0,

if((FiscalYear-[First Year Delivery])>0 and (FiscalYear-[First Year Delivery])<10,'0-9',

if((FiscalYear-[First Year Delivery])>=10 and (FiscalYear-[First Year Delivery])<20,'10-19',

if((FiscalYear-[First Year Delivery])>=20 and (FiscalYear-[First Year Delivery])<30,'20-29',

if((FiscalYear-[First Year Delivery])>=30,'30+',

)))))) as [Age Bucket]

From YourSource

Where not IsNull([First Year Delivery]);

Re: Expressions - Ignore rows with missing field values

Tue, 02 Dec 2014 22:21:56 GMT

Perfect, thanks Marco!