Updating Dates Based on Dimensions and preceding Date in Qlik Sense

[yh2023]

Hello everyone,

I'm trying to update a column called PRIORITY_DATE based on specific conditions and seeking guidance or suggestions on how to approach this.

I have a dataset structured as -

PTNO | DOCTOR | APPT_DATE
1 | A1 | 01/21/2021
1 | A1 | 01/30/2021
1 | A1 | 05/02/2021
1 | A1 | 06/04/2021
1 | A2 | 07/04/2021
1 | A1 | 10/04/2021
2 | A2 | 08/04/2021

Required Output:

Date format - MM/DD/YYYY

PTNO | DOCTOR | APPT_DATE | PRIORITY_DATE
1 | A1 | 01/21/2021 | 01/21/2021
1 | A1 | 01/30/2021 | 01/21/2021
1 | A1 | 05/02/2021 | 05/02/2021
1 | A1 | 06/04/2021 | 05/02/2021
1 | A2 | 07/04/2021 | 07/04/2021
1 | A1 | 10/04/2021 | 10/04/2021
2 | A2 | 08/04/2021 | 08/04/2021
I need to update the 'PRIORITY_DATE' column based on the following conditions:

• Update 'PRIORITY_DATE' for each 'PTNO' and 'DOCTOR' based on 'APPT_DATE'.
• If the previous appointment's 'PRIORITY_DATE' is not within the past three months of the current 'APPT_DATE' for the same 'PTNO' and 'DOCTOR', select 'APPT_DATE' as the new 'PRIORITY_DATE'.
  
  In the case of 'APPT_DATE' = 05/02/2021, the preceding 'PRIORITY_DATE' was 01/21/2021. However, the difference between 01/21/2021 and 05/02/2021 exceeds three months. Hence, the 'PRIORITY_DATE' is updated to 05/02/2021. Similarly, for 06/04/2021, the previous 'PRIORITY_DATE' is 05/02/2021, and as the difference is within three months, 05/02/2021 remains the priority date for 06/04/2021.
  Now, considering 10/04/2021, the preceding 'PRIORITY_DATE' is 05/02/2021. Given that the difference exceeds three months between these dates, the 'PRIORITY_DATE' should be updated to 10/04/2021

I've tried the following script:

LOAD
*,
if(month(APPT_DATE) - month(firstvalue(APPT_DATE)) <= 3, firstvalue(APPT_DATE), APPT_DATE)
Resident TABLE
group by PTNO, DOCTOR
order by APPT_DATE;

but this would result in 
PTNO | DOCTOR | APPT_DATE | PRIORITY_DATE
1 | A1 | 01/21/2021 | 01/21/2021
1 | A1 | 01/30/2021 | 01/21/2021
1 | A1 | 05/02/2021 | 05/02/2021
1 | A1 | 06/04/2021 | 06/04/2021 --> WRONG
1 | A2 | 07/04/2021 | 07/04/2021
1 | A1 | 10/04/2021 | 10/04/2021
2 | A2 | 08/04/2021 | 08/04/2021

Any insights, suggestions, or alternative approaches would be greatly appreciated!

Thank you in advance for the help.

[Ahidhar]

try this 

tab:
load
PTNO,Doctor,PTNO&'-'&Doctor as key,date(date#(APPT_DATE,'MM/DD/YYYY'),'MM/DD/YYYY') as APPT_DATE
;
load * Inline
[
PTNO,Doctor,APPT_DATE
1 , A1 , 01/21/2021
1 , A1 , 01/30/2021
1 , A1 , 05/02/2021
1 , A1 , 06/04/2021
1 , A2 , 07/04/2021
1 , A1 , 10/04/2021
2 , A2 , 08/04/2021
];

NoConcatenate
tab1:
load *
resident tab
order by key;
drop table tab;

tab2:
load PTNO,Doctor,APPT_DATE,PRIORITY_DATE;
load *,
if(key<>previous(key),APPT_DATE,
if(fabs(month(previous(APPT_DATE))-month(APPT_DATE))<3,Previous(APPT_DATE),APPT_DATE)) as PRIORITY_DATE
Resident tab1;
drop table tab1;

[yh2023]

Hi @Ahidhar 

The issue here is instead of 

if(fabs(month(previous(APPT_DATE))-month(APPT_DATE))<3,Previous(APPT_DATE),APPT_DATE)) as PRIORITY_DATE

I need something like this

if(fabs(month(previous(PRIORITY_DATE))-month(APPT_DATE))<3,Previous(PRIORITY_DATE),APPT_DATE)) as PRIORITY_DATE

however this wont work as PRIORITY_DATE is not defined

[Ahidhar · Accepted solution]

replace previous with peek 

if(fabs(month(peek(PRIORITY_DATE))-month(APPT_DATE))<3,Peek(PRIORITY_DATE),APPT_DATE)) as PRIORITY_DATE

[yh2023]

@Ahidhar 
Great it works! Thank you!

[paymydoctorpage]

 To show one record per DoctorsBHF but there could be multiple records with the same DoctorsBHF value.