Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Not applicable

Is Aggr what I need, or something else?

Hi,

I'm trying to return BMI values for a user who ONLY have values in the year 2009 and 2010.  So my table would look like:

LOAD * inline

[     UserID, Year, BMI

      A, 2010, 24.5

      A, 2019, 22.1

      B, 2010, 20.2

      B, 2009, 18.8

      C, 2010, 19.2

      C, 2009,    -           ]

So for example, user C wouldn't be in the cohort because they have no value for 2009.  I thought about using an if statement in the script, like...

if((BMI <> Null() and [Screening Year]='2010')

               and(BMI <> Null() and [Screening Year]='2009'), [External ID]) as [BMI Cohort],

...but when I create a table box to check my results, it doesn't seem to take out the folks who have null values.

I also thought about creating a straight table with a calculated dimension using Aggr, but I'm not sure if this is what I need to do.  Any thoughts?

Tags (1)
1 Solution

Accepted Solutions

Re: Is Aggr what I need, or something else?

An inline load doesn't generate nulls, but empty strings. So BMI <> Null() and isnull(BMI) don't do what you expect. Try if(len(trim(BMI))=0 instead. Or try a set analysis expression. See attached example.


talk is cheap, supply exceeds demand
5 Replies

Re: Is Aggr what I need, or something else?

An inline load doesn't generate nulls, but empty strings. So BMI <> Null() and isnull(BMI) don't do what you expect. Try if(len(trim(BMI))=0 instead. Or try a set analysis expression. See attached example.


talk is cheap, supply exceeds demand
calvindk
Contributor III

Re: Is Aggr what I need, or something else?

If you want it to have nulls and work as you propose give the inline table an alias and do a resident load where you replace the empty strings with nulls.

Such as:

INLINE:

LOAD * inline

[     UserID, Year, BMI

      A, 2010, 24.5

      A, 2019, 22.1

      B, 2010, 20.2

      B, 2009, 18.8

      C, 2010, 19.2

      C, 2009,    -           ];

Load

     UserID,

     Year,

     If(BMI = '', NULL(), BMI) as BMI

Resident INLINE;

Drop table INLINE;

Not applicable

Re: Is Aggr what I need, or something else?

Hi,

Sorry, I'm just using the inline table as an example.  The actual file that I am loading from is an Excel file with two worksheets; one for 2009 and one for 2010. 

Someone might have a BMI measurement in 2009, but not in 2010, so the value on the Excel file is blank, and blank when I load it in QV.  Therefore I don't want to include them in the cohort.

I haven't used set analysis before but it sounds like I might have to do so...

calvindk
Contributor III

Re: Is Aggr what I need, or something else?

If its not inline then you can set them to NULL during load.

If(BMI = '', NULL(), BMI) as BMI

Or if you want to have easy selectability (not in this case but can be useful otherwise )

If(BMI = '', 'MissingBMI', BMI) as BMI

Set analysis is a very strong tool, and you should familiarize yourself with it.

Not applicable

Re: Is Aggr what I need, or something else?

The set analysis expression in your script works!  I've been beating my head against the desk all morning trying to figure this out but you have solved it.

The problem is...I don't know WHY it works.  I've never worked with set analysis, and the syntax looks extremely confusing.

Thank you greatly!!

Community Browser