Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Not applicable

## Expressions - Ignore rows with missing field values

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.

1 Solution

Accepted Solutions
MVP

Hi,

something like

[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]);

4 Replies
MVP

in the script:

Where not IsNull([First Year Delivery])

or

Where Len([First Year Delivery]) > 0

or

Where [First Year Delivery] <> ''

Not applicable
Author

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]

MVP

Hi,

something like

[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]);

Not applicable
Author

Perfect, thanks Marco!

Community Browser