topic Re: Dates with different formats in same column.. in QlikView

Dates with different formats in same column..

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

Hello guys, I have a quick one.... I am having an issue with the dates i am trying to load.. i have a column cancel_date which have dates in two different formats in the same column.. one is 03-APR-2016 and the other one is 2016/03/01 00:00:0000..

When i am loading this QV is reading it in the string format.. and any condition given based on this is not working..

The current issue is, i have one more date which is "date" - and my condition is as follows..

If( Date <= [Cancel Date] , 'AA' , 'BB').. But QV is returning BB even at the places where the answer is AA..

PFA excel sheet where i downloaded the data for both the dates... I have tried loading the cancel date with date, date#, floor functions but nothing worked.. but for the date field loading it using Floor(Date(date)) as date..

Re: Dates with different formats in same column..

swuehl — Thu, 14 Apr 2016 21:26:04 GMT

Try with the Alt() function:

LOAD *,
If( Date <= [Cancel Date] , 'AA' , 'BB') as Flag;
LOAD Date(Alt(Date#(Date,'DD-MMM-YYYY'),Date#(Date,'MM/DD/YYYY hh:mm:ss'),Date(Date),Date))as Date,
  Date(Alt( Date#([Cancel Date],'DD-MMM-YYYY'),Date#(  [Cancel Date],'YYYY/MM/DD hh:mm:ss'),Date(  [Cancel Date]),  [Cancel Date])) as   [Cancel Date] 
   
FROM

(biff, embedded labels, table is Sheet1$);

Re: Dates with different formats in same column..

maahivee — Fri, 15 Apr 2016 20:23:34 GMT

Thank You Swuehl..