topic Re: Difference between dates - problem! in QlikView

Difference between dates - problem!

jlampard40 — Sat, 16 Nov 2024 02:21:17 GMT

Hi guys. I have two dates I want to calculate the days difference between.

The first date field is called [WBR_Event_Date] and the second field is called [WBR_Clock_Reset_Date]. I need to find out the days between the two. This seems easy enough. However, within the WBR_Event_Date values is a date value of 01/01/2099 (this represents non booked event date). I'd like to somehow replace this value with current date (today's date every time we load the daily data). I can then work out the difference between the two dates and, where event date = 01/01/2099 it's replaced with current date so we can derive 'Total days waiting to date' as an additional field to add into my table.

How do I go about firstly replacing all of the 01/01/2099 dates with current (today's date) and then calculating the difference between this and the other field I've mentioned above? Having problems...! Really appreciate your help.

Re: Difference between dates - problem!

jaibau1993 — Mon, 30 Sep 2019 11:24:45 GMT

Hi!

On the Script side you may use RangeMin when loading WBR_Event_Date:

LOAD

...

RangeMin(Today(), WBR_Event_Date) as WBR_Event_Date,

...

Where I have supposed that WBR_Event_Date is lower or equal than today except when it is 01/01/2099. If WBR_Event_Date can be greater than today then use a simple if statement in the LOAD: if(WBR_Event_Date = MakeDate(2099), Today(), WBR_Event_Date) or similar

Regards,

Jaime.

Re: Difference between dates - problem!

jlampard40 — Mon, 30 Sep 2019 11:43:43 GMT

Thanks! I can't seem to get it to work. This is my load script at the moment. I've had to convert the two date fields (Clock Reset and Event Date) into DD/MM/YYYY as the original data came in the format YYYY-MM-DD and the end user is not happy about viewing dates this way.

Can you advise how I modify this script to find and replace all of the 01/01/2099's as today's date (current date as of day script is run). Many thanks!

LOAD [Event key] as WBR_Event_Key,
Target as WBR_Target,
[42d_Breach] as WBR_42d_Breach,
[14d_Breach] as WBR_14d_Breach,
[CRIS No.] as WBR_CRIS_No,
Surname as WBR_Surname,
Forenames as WBR_Forenames,
DOB as WBR_DOB,
Mod as WBR_Mod,
Examination as WBR_Examination,
Room as WBR_Room,
date(floor(timestamp([Clock Reset Date])),'DD/MM/YYYY') as WBR_Clock_Reset_Date,
date(floor(timestamp([Event Date])),'DD/MM/YYYY') as WBR_Event_Date,
Days_Waiting as WBR_Days_Waiting,
Appointment as WBR_Appointment,
[Patient Type Des] as WBR_Patient_Type_Des,
Urgency as WBR_Urgency,
GA as WBR_GA,
Cardiac as WBR_Cardiac,
Vascular as WBR_Vascular,
AgeGrp as WBR_AgeGrp,
[42d_Breach_Status] as WBR_42_Breach_Status,
Target_Month as WBR_Target_Month

Re: Difference between dates - problem!

jaibau1993 — Mon, 30 Sep 2019 11:59:00 GMT

Hi! In red my modifications:

LOAD [Event key] as WBR_Event_Key,
Target as WBR_Target,
[42d_Breach] as WBR_42d_Breach,
[14d_Breach] as WBR_14d_Breach,
[CRIS No.] as WBR_CRIS_No,
Surname as WBR_Surname,
Forenames as WBR_Forenames,
DOB as WBR_DOB,
Mod as WBR_Mod,
Examination as WBR_Examination,
Room as WBR_Room,
date(floor(timestamp([Clock Reset Date])),'DD/MM/YYYY') as WBR_Clock_Reset_Date,
if(Year([Event Date]) = 2099, Today(), date(floor(timestamp([Event Date])),'DD/MM/YYYY')) as WBR_Event_Date,
Days_Waiting as WBR_Days_Waiting,
Appointment as WBR_Appointment,
[Patient Type Des] as WBR_Patient_Type_Des,
Urgency as WBR_Urgency,
GA as WBR_GA,
Cardiac as WBR_Cardiac,
Vascular as WBR_Vascular,
AgeGrp as WBR_AgeGrp,
[42d_Breach_Status] as WBR_42_Breach_Status,
Target_Month as WBR_Target_Month

When a LOAD statement is executed, the table is read row by row. Thus, you only have to check if the content of [Event Date] is 01/01/2099 and modify it if so (you don't need to "find and replace" after the load statement).

Regards,

Jaime.

Re: Difference between dates - problem!

jlampard40 — Mon, 30 Sep 2019 12:06:16 GMT

Works a treat! Thanks so much. Many thanks.

Re: Difference between dates - problem!

jlampard40 — Mon, 30 Sep 2019 12:34:42 GMT

Cheeky addition...if I then wanted to highlight in red font, those dates which are 01/01/2099 (now changed to today's date) is there any way of doing this too? I just want users to realise that even though there is a date in this field, it's only today's date and NOT an actual event date. Thanks

Re: Difference between dates - problem!

jaibau1993 — Mon, 30 Sep 2019 15:16:07 GMT

You just need a flag that marks whether a date is modified or not

if(Year([Event Date]) = 2099, 1, 0) as Flag_ModifiedDate
Days_Waiting as WBR_Days_Waiting,
Appointment as WBR_Appointment,
[Patient Type Des] as WBR_Patient_Type_Des,
Urgency as WBR_Urgency,
GA as WBR_GA,
Cardiac as WBR_Cardiac,
Vascular as WBR_Vascular,
AgeGrp as WBR_AgeGrp,
[42d_Breach_Status] as WBR_42_Breach_Status,
Target_Month as WBR_Target_Month

Now use that flag in your charts to build a colouring condition.

Regards,

Jaime.