topic Re: Comparing 2 date fields to flag the count in QlikView

Comparing 2 date fields to flag the count

archire2102 — Thu, 05 Apr 2018 01:55:46 GMT

Hi,

I am trying to count the number of rows where Date1 is equals to Date2. It seems really simple but the flag has only 0 even if there are so many matching dates.

if(Date(Date#(Date1,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY') = Date(Date#(Date2,'DD/MM/YYYY'),'DD/MM/YYYY'), 1, 0) As FlagField

Re: Comparing 2 date fields to flag the count

Anonymous — Thu, 05 Apr 2018 03:39:50 GMT

You have time also in the Date1 field. Get rid of the time and then compare it should work.

Re: Comparing 2 date fields to flag the count

archire2102 — Sun, 08 Apr 2018 10:30:24 GMT

Thanks Aadil but that's why I have used Date and Date# function but still not working.

Re: Comparing 2 date fields to flag the count

sasiparupudi1 — Sun, 08 Apr 2018 16:25:09 GMT

You have to remove the time component

If(Date(Floor(Date#(Date1,'DD/MM/YYYY hh:mm')),'DD/MM/YYYY') = Date(Date#(Date2,'DD/MM/YYYY'),'DD/MM/YYYY'), 1, 0) As FlagField

I suggest you to put the individual expressions into 2 text boxes and see if the formats are coming out as expected

hth

Sasi

Re: Comparing 2 date fields to flag the count

pradosh_thakur — Sun, 08 Apr 2018 16:59:39 GMT

if(floor(timestamp#(Date1,'DD/MM/YYYY hh:mm')) = floor(Date#(Date2,'DD/MM/YYYY')), 1, 0) As FlagField