Dates with different formats in same column..

[maahivee]

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.. 

[swuehl · Accepted solution]

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

[maahivee]

Thank You Swuehl..