Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to use a date formatted MM/DD/YYYY and compare it to today's date in an expression. I would like to have all dates before the current date flagged and all the dates after the current date to remain unflagged. The expression that is not working for me is as follows:
if( DateField < Today(), 1,0)
I'm just trying to flag all dates before today as 1, all dates after today as 0.
Any help would be greatly appreciated.
Hi,
I agree that
if( DateField < Today(), 1,0)
not working suggests that DateField is Text and not Dual.
So your approach
If(Date(Date#(DateField,'DD/MM/YYYY'))<Date(Today()),1,0)
should work fine.
I'd like to add that the outer date() functions in this case don't change the value and thus should not influence the comparison, i.e.
If(Date#(DateField,'DD/MM/YYYY')<Today(),1,0)
should work as well and can even be abbreviated to
-(Date#(DateField,'DD/MM/YYYY')<Today())
hope this helps
regards
Marco
I would simply wrap both sides of that expression with the Num() function to compare apples to apples.
If( Num(DateField) < Num(Today()), 1, 0) as DateFlag
Good luck
Oscar
q:
load
DateField,
if(DateField < date(Today(), 'MM/DD/YYYY'),1,0) as Flag;
load
date(floor(today() + rand()*100 - rand()*100), 'MM/DD/YYYY') as DateField
autogenerate 10000;
Load
*,
If(Date(Date#(DateField,'DD/MM/YYYY'))<Date(Today()),1,0) as Flag
Inline
[
DateField
01/01/2014
14/05/2014
10/11/2014
15/12/2014
01/01/2015
];
Or Simply use below expression at UI end
If(Date(Date#(DateField,'DD/MM/YYYY'))<Date(Today()),1,0)
Hi,
I agree that
if( DateField < Today(), 1,0)
not working suggests that DateField is Text and not Dual.
So your approach
If(Date(Date#(DateField,'DD/MM/YYYY'))<Date(Today()),1,0)
should work fine.
I'd like to add that the outer date() functions in this case don't change the value and thus should not influence the comparison, i.e.
If(Date#(DateField,'DD/MM/YYYY')<Today(),1,0)
should work as well and can even be abbreviated to
-(Date#(DateField,'DD/MM/YYYY')<Today())
hope this helps
regards
Marco
Thanks for the well formatted response.
You're welcome
regards
Marco