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

Return Most Recent Obs

Hi,

I'm working with a synthetic data set in an EAV format, entity-attribute-value.  I'd like to return the most recent health measure name and value for each individual. Item level is the base table that I loaded in, I want to have a second table that just shows the most recent.

Here's a sample:

   

IDGenderagevaluehealthMeasureobsMonth
1Female189ESS_score3
1Female18108ANAM4
1Female18NoBP_hypertensive9
1Female18NoBP_hypertensive6
1Female18YesappropriateScreen6
1Female18YesappropriateScreen9
1Female18GoodcardioTest10

So for person with ID one, ideally I would have 5 rows, dropping the ones with BP hypertensive and month 6 and appropriate screen and month 6.  (This data set has 100s of people in it...)

What I have hasn't been working:

[MostRecentObs]:

Load

ID,

    ObsMonth,

    Gender,

     Age,

    healthMeasure,

    value

Resident ItemLevel

where ObsMonth= max(ObsMonth)

Group by ID,

Gender,

"age",

healthMeasure

Any help would be appreciated!

Best,

Tanya

1 Solution

Accepted Solutions
effinty2112
Master
Master

Hi Tanya,

Does this chart give you what you need?

ID Gender age FirstSortedValue(value,-obsMonth) healthMeasure Max(obsMonth)
1Female18108ANAM4
1Female18YesappropriateScreen9
1Female18NoBP_hypertensive9
1Female18GoodcardioTest10
1Female189ESS_score3

Cheers

Andrew

View solution in original post

8 Replies
sunny_talwar

May be this

Table:

LOAD ID,

    Gender,

    age,

    value,

    healthMeasure,

    obsMonth

FROM

[https://community.qlik.com/thread/268946]

(html, codepage is 1252, embedded labels, table is @1);

Right Join (Table)

LOAD ID,

Gender,

age,

healthMeasure,

Max(obsMonth) as obsMonth

Resident Table

Group By ID, Gender, age, healthMeasure;

settu_periasamy
Master III
Master III

Maybe like this

[MostRecentObs]:

Load

ID,

Gender,

    Age,

    healthMeasure,

    value,

    max(ObsMonth) as ObsMonth

Resident ItemLevel Group by ID,Gender,Age,healthMeasure,value;

Not applicable
Author

I've tried that and it returns all rows.

effinty2112
Master
Master

Hi Tanya,

Does this chart give you what you need?

ID Gender age FirstSortedValue(value,-obsMonth) healthMeasure Max(obsMonth)
1Female18108ANAM4
1Female18YesappropriateScreen9
1Female18NoBP_hypertensive9
1Female18GoodcardioTest10
1Female189ESS_score3

Cheers

Andrew

Not applicable
Author

Yes, that's it!  I've gotten close with the reply from Sunny Talwar, however, that solution doesn't limit the records down to only to return the max obs record as your does.  My solution was to create an additional table with a where clause when obsmonth=max(obsmonth) but I haven't been able to get that to work, for some reason no other tables are being produced after that join, am I missing a semi colon somewhere? 

The other issue this introduces is that Qlik is creating a synthetic key on obsmonth which doesn't really make sense, I would like to relate the tables but use ID instead.

Thanks much for any assistance!

Tanya

sunny_talwar

May be create a flag for the max Month?

Capture.PNG

effinty2112
Master
Master

Hi Tanya,

          Glad to be of help. Once you're sure you've got the answer you need please close the thread close by marking a response as correct and if you're feeling generous you can also the mark responses of those who have helped as helpful.

Cheers

Andrew

Not applicable
Author

Hi Andrew,

Could you provide the code you used to produce the table in your last post? 

Best,

Tanya