topic Re: Date Issues in Script in QlikView

Date Issues in Script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have two date columns in a table and our want QlikView to compare the two dates for each row but it is not doing correctly. my guess is that the two sets of dates are in a different format,so I tried converting both to a number using Num or converting both to Date but still having issues. Any ideas?

Re: Date Issues in Script

sunny_talwar — Wed, 21 Dec 2016 16:54:06 GMT

Formatting should not stop you from comparing them, how exactly are you doing the comparison? If statement, set analysis?

Re: Date Issues in Script

Anonymous — Wed, 21 Dec 2016 16:56:21 GMT

if statement. So I have two sets of dates in the table I am loading and when I load in the where clause I say only include those records where date1>date2 but it does not seem to understand that correctly since it still loads records where date 1<date 2

Re: Date Issues in Script

Anonymous — Wed, 21 Dec 2016 16:58:00 GMT

I am using QlikView script.

Re: Date Issues in Script

sunny_talwar — Wed, 21 Dec 2016 17:05:23 GMT

Both the dates are in the same table? Does both of them understood by QlikView as dates? If not, you might need to use Date#() function to help QlikView understand that they are dates

Why don’t my dates work?

Get the Dates Right‌

Can you share the script you are using?

Re: Date Issues in Script

vishsaggi — Wed, 21 Dec 2016 17:07:33 GMT

can you share your script how you doing ? Were you using SQL Script ?

May be you want to try converting the dates using

LOAD *;

SQL SELECT yourcolumnlist

FROM Tablename

WHERE

Cast(Date1 AS Date) > Cast(Date2 AS Date);

Re: Date Issues in Script

Anonymous — Wed, 21 Dec 2016 17:15:51 GMT

Let me see if Date#(). I have a feeling that might be it.

Re: Date Issues in Script

Anonymous — Wed, 21 Dec 2016 17:25:39 GMT

Did not work sadly. The relevant part of the script is below.

dateforoppsbeforelead:
Load
*,
LEADCREATEDATE as LEADCREATEDATEdup
resident onlymodifydateforopps where Date#(LEADCREATEDATE) >Date#(STAGEMODIFYDATE) ;
Drop Table onlymodifydateforopps;

Re: Date Issues in Script

sunny_talwar — Wed, 21 Dec 2016 17:28:24 GMT

You have to provide a format within Date#() function. What are the formats of your field? Assuming it is DD-MMM-YYYY, you will do it like this:

dateforoppsbeforelead:
Load *,
LEADCREATEDATE as LEADCREATEDATEdup
Resident onlymodifydateforopps

Where Date#(LEADCREATEDATE, 'DD-MMM-YYYY') >Date#(STAGEMODIFYDATE, 'DD-MMM-YYYY') ;

Drop Table onlymodifydateforopps;

Re: Date Issues in Script

Anonymous — Wed, 21 Dec 2016 17:30:22 GMT

I will check now. Thank you

Re: Date Issues in Script

Anonymous — Wed, 21 Dec 2016 17:45:55 GMT

I tried this but still did not work. Could it be anything else?

Re: Date Issues in Script

Anonymous — Wed, 21 Dec 2016 17:47:02 GMT

My version of QlikView did not understand the cast command

Re: Date Issues in Script

vishsaggi — Wed, 21 Dec 2016 18:03:35 GMT

May be try this? Extending Sunnys expression.

LOAD *

Where LEADCREATEDATEdup > STAGEMODIFYDATE;

Load *,
Date#(LEADCREATEDATE, 'DD-MMM-YYYY') as LEADCREATEDATEdup,

Date#(STAGEMODIFYDATE, 'DD-MMM-YYYY') as STAGEMODIFYDATEdup
Resident onlymodifydateforopps ;

Drop Table onlymodifydateforopps;

Re: Date Issues in Script

sunny_talwar — Wed, 21 Dec 2016 18:19:56 GMT

What format is STAGEMODIFYDATE and LEADCREATEDATE are in?

Re: Date Issues in Script

Anonymous — Wed, 21 Dec 2016 18:22:18 GMT

MM/DD/YYYY which is what I used instead of the one you gave me

Re: Date Issues in Script

Anonymous — Wed, 21 Dec 2016 18:25:15 GMT

To be precise, the original format looks something like this:

12/21/2015 5:04:57 PM

To me the time of day piece is not important really.

Re: Date Issues in Script

Anonymous — Wed, 21 Dec 2016 18:25:34 GMT

This is the format for both of them

Re: Date Issues in Script

Anonymous — Wed, 21 Dec 2016 18:27:17 GMT

Did you mean the top portion to say

LOAD *

Where LEADCREATEDATEdup > STAGEMODIFYDATEdup;

Re: Date Issues in Script

sunny_talwar — Wed, 21 Dec 2016 18:37:22 GMT

Then try this:

dateforoppsbeforelead:
Load *,
LEADCREATEDATE as LEADCREATEDATEdup
Resident onlymodifydateforopps

Where Date#(LEADCREATEDATE, 'MM/DD/YYYY h:mm:ss TT') >Date#(STAGEMODIFYDATE, 'MM/DD/YYYY h:mm:ss TT') ;

Drop Table onlymodifydateforopps;

Re: Date Issues in Script

Anonymous — Wed, 21 Dec 2016 18:38:20 GMT

should I try timestamp instead or it does not matter?