Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
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;
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...
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.
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!!