topic Re: Adding a field with latest value and date in QlikView

Adding a field with latest value and date

marunio007 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have 2 tables:

Visit:
Load * Inline [
Patient, VisitDate
1,2014-12-18
1,2015-04-09
1,2015-05-14
2,2014-01-24
2,2014-07-25
2,2015-01-23
]
;
ACR:
Load * Inline [
Patient, ACRDate, ACR
1,2014-08-12,24.1
1,2014-12-03,36.5
1,2015-04-02,12.8
1,2015-04-30,11.3
2,2014-01-17,41
2,2014-07-21,43.2
2,2016-01-21,34.4
]

For each visit I need to know what the last ACR prior to the visit was and when.
I would like to attach 2 columns to table Visit: LastACRDate and LastACR.

So the Visit table will look like this:

Patient	VisitDate	LastACR	LastACRDate
1	2014-12-18	36.5	2014-12-03
1	2015-04-09	12.8	2015-04-02
1	2015-05-14	11.3	2015-04-30
2	2014-01-24	41	2014-01-17
2	2014-07-25	43.2	2014-07-21
2	2015-01-23	43.2	2014-07-21

Thank you kindly for any help.

Re: Adding a field with latest value and date

sunny_talwar — Wed, 08 Feb 2017 20:23:08 GMT

May be this:

Visit:

Load * Inline [

Patient, VisitDate

1,2014-12-18

1,2015-04-09

1,2015-05-14

2,2014-01-24

2,2014-07-25

2,2015-01-23

];

ACR:

Load * Inline [

Patient, ACRDate, ACR

1,2014-08-12,24.1

1,2014-12-03,36.5

1,2015-04-02,12.8

1,2015-04-30,11.3

2,2014-01-17,41

2,2014-07-21,43.2

2,2016-01-21,34.4

];

Left Join (Visit)

LOAD Patient,

Date(ACRDate + IterNo() - 1) as VisitDate,

ACR as LastACR,

ACRDate as LastACRDate

While ACRDate + IterNo() - 1 <= EndDate;

LOAD *,

Date(If(Patient = Previous(Patient), Previous(ACRDate) - 1, Today())) as EndDate

Resident ACR

Order By Patient, ACRDate desc;

DROP Table ACR;

Re: Adding a field with latest value and date

marunio007 — Thu, 09 Feb 2017 16:57:17 GMT

That is so clever.

It works!

Thank you very much.

Re: Adding a field with latest value and date

sunny_talwar — Thu, 09 Feb 2017 17:29:48 GMT

Did you mean to mark response as correct?

Re: Adding a field with latest value and date

marunio007 — Thu, 09 Feb 2017 17:32:36 GMT

I did. The approval delay played a role here....

Thanks again!

Re: Adding a field with latest value and date

sunny_talwar — Thu, 09 Feb 2017 17:40:26 GMT

It still seems that you have marked your response as correct here

Re: Adding a field with latest value and date

vishsaggi — Thu, 09 Feb 2017 17:41:50 GMT

Marek, You marked your comment as Correct Response? Did you get the resolution already before Sunny helped you?

Re: Adding a field with latest value and date

marunio007 — Thu, 09 Feb 2017 18:29:25 GMT

My apologies to the both of you.- mistake corrected (I hope)

I promise improvement in my next thread...

Re: Adding a field with latest value and date

sunny_talwar — Thu, 09 Feb 2017 18:36:57 GMT

Apologize not accepted . Just kidding!! Didn't want to harass, but the idea is that others can benefit when they land on this page. Seeing a thanks as the correct response is not very helpful and that's why I harassed you to mark the correct response.

Best,

Sunny