Adding a field with latest value and date

[marunio007]

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.

[sunny_talwar · Accepted solution]

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;

[marunio007]

That is so clever.

It works!

Thank you very much.

[sunny_talwar]

Did you mean to mark response as correct?

[marunio007]

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

Thanks again!

[sunny_talwar]

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

[vishsaggi]

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

[marunio007]

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

I promise improvement in my next thread...

[sunny_talwar]

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