Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

match dates minus timestamp

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'; 

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: match dates minus timestamp

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,

4 Replies
MVP
MVP

Re: match dates minus timestamp

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

HI

Try like this

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

or

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

Not applicable

Re: match dates minus timestamp

That absolutely fixed my problem.  Thank you very much!

Not applicable

Re: match dates minus timestamp

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

Community Browser