Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Re: Expressions - Ignore rows with missing field values

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

4 Replies

Re: Expressions - Ignore rows with missing field values

in the script:

Where not IsNull([First Year Delivery])

or

Where Len([First Year Delivery]) > 0

or

Where [First Year Delivery] <> ''

Not applicable

Re: Expressions - Ignore rows with missing field values

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

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

Re: Expressions - Ignore rows with missing field values

Perfect, thanks Marco!

Community Browser