Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
Formatting should not stop you from comparing them, how exactly are you doing the comparison? If statement, set analysis?
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
I am using QlikView script.
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
Can you share the script you are using?
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);
Let me see if Date#(). I have a feeling that might be it.
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;
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;
I will check now. Thank you