Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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

View solution in original post

5 Replies
Gysbert_Wassenaar

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
Creator III
Creator III

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
Author

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
Creator III
Creator III

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
Author

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!!