Date Issues in Script

[Anonymous]

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?

[sunny_talwar]

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

[Anonymous]

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

[Anonymous]

I am using QlikView script.

[sunny_talwar]

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?

[vishsaggi]

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

[Anonymous]

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

[Anonymous]

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;

[sunny_talwar · Accepted solution]

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;

[Anonymous]

I will check now. Thank you