topic Return Most Recent Obs in QlikView

Return Most Recent Obs

Tue, 25 Jul 2017 19:44:45 GMT

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:

ID	Gender	age	value	healthMeasure	obsMonth
1	Female	18	9	ESS_score	3
1	Female	18	108	ANAM	4
1	Female	18	No	BP_hypertensive	9
1	Female	18	No	BP_hypertensive	6
1	Female	18	Yes	appropriateScreen	6
1	Female	18	Yes	appropriateScreen	9
1	Female	18	Good	cardioTest	10

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

Re: Return Most Recent Obs

sunny_talwar — Wed, 26 Jul 2017 00:57:18 GMT

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;

Re: Return Most Recent Obs

settu_periasamy — Wed, 26 Jul 2017 01:33:42 GMT

Maybe like this

[MostRecentObs]:
Load
ID,
Gender,
    Age,
    healthMeasure,
    value,
    max(ObsMonth) as ObsMonth

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

Re: Return Most Recent Obs

Wed, 26 Jul 2017 18:34:49 GMT

I've tried that and it returns all rows.

Re: Return Most Recent Obs

effinty2112 — Wed, 26 Jul 2017 20:59:03 GMT

Hi Tanya,

Does this chart give you what you need?

ID	Gender	age	FirstSortedValue(value,-obsMonth)	healthMeasure	Max(obsMonth)
1	Female	18	108	ANAM	4
1	Female	18	Yes	appropriateScreen	9
1	Female	18	No	BP_hypertensive	9
1	Female	18	Good	cardioTest	10
1	Female	18	9	ESS_score	3

Cheers

Andrew

Re: Return Most Recent Obs

Thu, 27 Jul 2017 13:04:09 GMT

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

Re: Return Most Recent Obs

sunny_talwar — Thu, 27 Jul 2017 15:32:30 GMT

May be create a flag for the max Month?

Re: Return Most Recent Obs

effinty2112 — Thu, 27 Jul 2017 15:55:25 GMT

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

Re: Return Most Recent Obs

Thu, 27 Jul 2017 16:05:39 GMT

Hi Andrew,

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

Best,

Tanya