Compare if the values in two date columns are the same using an IF statement

[R2024]

Hi All,

I am still new to Qlikview.

I'm trying to compare two dates to display either a "Y" or "N" based on whether they are the same. I have formatted both date fields, but I’m always getting "N" in the result.

I tried the DATE and FLOOR function to remove the timestamp from both fields:

DATE([RECORDED_TIME], 'MM/DD/YYYY') as Recorded_Date
FLOOR([RECORDED_TIME]) as Recorded_Date_Floor

DATE([SURGERY_DATE], 'MM/DD/YYYY') as Surgery_Date
FLOOR([SURGERY_DATE]) as Surgery_Date_Floor

Then, I applied the following calculation to compare the dates:
IF(Recorded_Date = Surgery_Date, 'Y', 'N')
IF(Recorded_Date_Floor = Surgery_Date_Floor, 'Y', 'N')

However, this still doesn't work as expected. Could someone please guide me and let me know if I'm making any mistakes here? Is there a sample or best practice I could follow to compare dates correctly?

Thanks in advance for your help.

[Chanty4u]

Try this 

IF(FLOOR([RECORDED_TIME]) = FLOOR([SURGERY_DATE]), 'Y', 'N')

Or try below 

IF(DATE(FLOOR([RECORDED_TIME])) = DATE(FLOOR([SURGERY_DATE])), 'Y', 'N')

[BPiotrowski]

Hi, @R2024 

Maybe this is what you are searching for?

if(Interval#(Date) <> Interval#(Date_2),false(),true())

[R2024]

Thank you for your responses, Chanty4u and BPiotrowski. Unfortunately, none of them worked. I am still not able to get the desired result. I get a zero or N for all of the solutions even though I have a few records that should show one or Y. Any other suggestions? I am out of ideas.

[Oleg_Troyansky]

Hi there,

It's hard to say without seeing the data... You can look at your date fields in the "raw" numeric format and compare them visually. The FLOOR comparison should produce reliable results if your date fields are truly formatted as date fields.

You can also post a small sample for us to look at

Cheers,

[BPiotrowski]

@R2024 

Try to interpret this field as Date in load script, and add date format to intevals. Give as sample of loading part and data for further debug.

[R2024]

Thank you all for your continued effort to help me.

I've attached the script and data. The Events table data and the Mobility table data are from the QVD, and one of the tables below after calculations is showing duplicated rows. I can't figure out where the issue lies with my comparison IF statements, and I've tried several approaches using INTERVAL,  FLOOR and DATE functions to get the 'Y' and 'N' values correct. I would like to do this in the script and not in an expression as I need to reuse it multiple times.

Also, could someone please provide a clear explanation of how dates work in QlikView and the different functions for dates and timestamps? Since QlikView doesn’t have explicit data types, it can be quite confusing.

Thanks again.

[Oleg_Troyansky]

Hi,

A detailed explanation of how dates and date functions work would be too long for this format... Try looking for blog articles that explain that - both here in Qlik Community, and outside of it.

In general, as a new developer, you could probably use a good tutorial book, to cover all the bases properly. I happen to be the author of one, and I can highly recommend it to you - it will take you from the basics of QlikView development to the most advanced development methodologies. Not too many people need to learn QlikView today (most clients move on to Qlik Sense), but you can actually benefit from my book:

QlikView Your Business

Enjoy,

Oleg Troyansky

[rubenmarin · Accepted solution]

Hi, basically real dates are numbers, and the unit represents a day, and the decimal part is the time.

So the date 0 is 30/12/1899, date 1 is 31/12/1899 date 0.5 is 30/12/1899 12:00.

An easy way to identify a value is a numebr is the default alingment in the table, if it's left-aligned is a text, right-aligned values are numbers.

In your screenshot StartDate and RecordedDate are left-aligned, meaning they are not numbers, just strings, and functions like Floor, interval or date only works with numbers.

Try with "Date(Floor(Date#(... ) as StartDate" to convert this field to a date.

And confirm how RecordedDate is created to really load a number.

You can also use Num#() to convert them back to numbers.

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/Interpr...

It will be nice to have a screenshot of the final dates loaded on the MobilitySurgeryDay to check the final format and confirm all dates are filled in the same row.