Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead 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
MarcoWedel

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

View solution in original post

4 Replies
MarcoWedel

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]

MarcoWedel

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

Not applicable
Author

Perfect, thanks Marco!