topic Re: match dates minus timestamp in QlikView

match dates minus timestamp

Wed, 07 Nov 2012 22:23:27 GMT

In my script, I'm attempting to count number of contacts where two fields, contact date and project date, equal each other. In cases where they are equal, I'm trying to tag that record as 'New to DB', else 'Other', per the example below. However, my script is not working. It recognizes no matches, even though I've found numerous matches offline in Excel. I suspect it is because both fields contain a timestamp appended to the date stamp. I tried overcoming this problem by removing all time references ('h:mm:ss TT'), leaving just the dates (see my date and timestamp variable settings below), but still no success. Can anyone help?

[Contact Date],
[Project Date],
IF([Project Date]=[Contact Date],'New to DB','Other') AS NewDB,

EXAMPLE of script variables (removes time references, leaves only the dates):

SET

DateFormat='M/D/YYY';

SET Timestamp

Format='M/D/YYY';

Re: match dates minus timestamp

swuehl — Wed, 07 Nov 2012 22:30:59 GMT

Your standard format settings are just formatting the text representation of your values, not changing the numeric value, still holding the time part.

edit: And if you change the standard format, QV might not correctly interprete the source data. Either set the standard format to the format of your source or use date#() or timestamp#() function with an approproate format code to interprete your source data correctly.

Try

[Contact Date],
[Project Date],
IF( daystart([Project Date])=daystart([Contact Date]),'New to DB','Other') AS NewDB,

Re: match dates minus timestamp

MayilVahanan — Wed, 07 Nov 2012 22:32:46 GMT

Try like this

if(Date(ContactDate,'DD/MM/YYYY') = Date(ProjectDate/'DD/MM/YYYY),'New','Others')

Use timeStamp(ContactDate,'DD/MM/YYYY')

Re: match dates minus timestamp

Wed, 07 Nov 2012 22:40:14 GMT

That absolutely fixed my problem. Thank you very much!

Re: match dates minus timestamp

Wed, 07 Nov 2012 22:42:19 GMT

Mayil, I'll write your suggestion down as an alternative technique. Thanks to both of you who replied.