Previous Date from other dataset

[asamariaekrickl]

Hi all,

I have two data sources.
One with Date and a client number and the other with PreviousDate and client number.

This is what I see when I have joined the two tables together.

The below is what I'm after. The closest PreviousDate prior to the Date.

Any ideas?

Thanks in advance

Regards Asa

[Anil_Babu_Samineni]

Can you explain better and what is the expected result

[asamariaekrickl]

Hi there,

Yes, of course I can 🙂
I only want one record for each Date and in the PreviousDate field I want the date which is closest in time prior to the Date.

So the Date is when a patient saw a doctor and the PreviousDate is when that same patient had his/her last appointment.
I then need to count the days between Date and PreviousDate but that is step 2 🙂

Please see the screenshot of the expected result.

Thanks
Asa

[PradeepReddy]

try something like this..

Source1:
Load *,
Patient_Id, Date_Latest From Source1;

Left Join(Source1)
Source2:
Load Patient_Id, max(Date) as Date_Prev  from source2 group by Patient_Id;

Final:
Load *, Date_Latest - Date_Prev as Days
Resident Source1;
Drop table Source1;

[asamariaekrickl]

Hi and thanks for your input!

My script now looks like this:

TmpCHIMENewPatient:
load
individual_client_name,
date(Min_Date) as Date
from
[..\..\1.Extraction\2.QVD\CHIMEKPI23Step1.qvd]
(qvd);

 join (TmpCHIMENewPatient)
//PreviousVisit
load
individual_client_name,
date(max([SC-Date])) as PreviousDate
from
[..\..\1.Extraction\2.QVD\CHIMEKPI23Step2.qvd]
(qvd)
group by individual_client_name;

But this results in the below.

Any ideas how to get the closest date prior to Date to show in the PreviousDate?

I have tried FirstSortedValue as well but no luck so far.

Thanks
Asa

[PradeepReddy]

Check the data granularity in both the tables, before joining.

 if possible can u  attach sample app?

[shreya_nadkarni]

Hi,

Script seems fine.

Kindly add left join(TmpCHIMENewPatient)  instead of join and give it a try.

Thanks

[asamariaekrickl]

Hi,

The Left join made no difference unfortunately and the result is still the same as before.

Any more ideas? 🙂

[shreya_nadkarni]

is it possible to attach a sample app with source?