Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
asamariaekrickl
Partner - Contributor III
Partner - Contributor III

Previous Date from other dataset

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.

Current view.png

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

Wanted view.png

Any ideas?

Thanks in advance

Regards Asa

8 Replies
Anil_Babu_Samineni

Can you explain better and what is the expected result
Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
asamariaekrickl
Partner - Contributor III
Partner - Contributor III
Author

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
Specialist II
Specialist II

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
Partner - Contributor III
Partner - Contributor III
Author

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.

New result.PNG

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
Specialist II
Specialist II

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

 if possible can u  attach sample app?

 

shreya_nadkarni
Partner - Creator
Partner - Creator

Hi,

Script seems fine.

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

Thanks

asamariaekrickl
Partner - Contributor III
Partner - Contributor III
Author

Hi,

 

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

Any more ideas? 🙂

shreya_nadkarni
Partner - Creator
Partner - Creator

is it possible to attach a sample app with source?