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

Is Aggr what I need, or something else?

Thu, 09 May 2013 14:29:48 GMT

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?

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

Gysbert_Wassenaar — Thu, 09 May 2013 14:48:44 GMT

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.

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

calvindk — Thu, 09 May 2013 14:52:14 GMT

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;

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

Thu, 09 May 2013 14:55:25 GMT

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

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

calvindk — Thu, 09 May 2013 15:14:43 GMT

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.

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

Thu, 09 May 2013 17:27:07 GMT

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