Create new field based on Date range

[karan_kn]

Please help me to create new field based on Date

Last 1 Year = New

Older than 1 Year= Old

LOAD * INLINE [

    ID, Date

    1, 1/20/2017

    2, 2/29/2017

    3, 3/26/2017

    4, 4/10/2017

    5, 10/14/2017

    6, 11/26/2017

    7, 12/2/2017

    8, 1/3/2018

    9, 3/10/2018

    10, 5/15/2018

];

Result:

[big_dreams · Accepted solution]

try below

Load *,if(date(Date)<date(addYears(Today(),-1)),'old','New') as flag;

LOAD * INLINE [

    ID, Date

    1, 1/20/2017

    2, 2/29/2017

    3, 3/26/2017

    4, 4/10/2017

    5, 10/14/2017

    6, 11/26/2017

    7, 12/2/2017

    8, 1/3/2018

    9, 3/10/2018

    10, 5/15/2018

];

Regards,

[prachisangewar]

Hi,

Try the below solution:

Load *,if(date#(Date,'M/D/YYYY')<date(addYears(Today(),-1),'M/D/YYYY'),'Old','New') as Date_Flag,

date(addYears(Today(),-1)) as prev_yrdate;

LOAD * INLINE [

    ID, Date

    1, 1/20/2017

    2, 2/29/2017

    3, 3/26/2017

    4, 4/10/2017

    5, 10/14/2017

    6, 11/26/2017

    7, 12/2/2017

    8, 1/3/2018

    9, 3/10/2018

    10, 5/15/2018

];

[vishalarote]

Maybe like this

Don't forget Mark as correct

Date:

LOAD *,Date(Date#(Date,'M/D/YYYY'),'M/D/YYYY') as D INLINE [

    ID, Date

    1, 1/20/2017

    2, 2/29/2017

    3, 3/26/2017

    4, 4/10/2017

    5, 10/14/2017

    6, 11/26/2017

    7, 12/2/2017

    8, 1/3/2018

    9, 3/10/2018

    10, 5/15/2018

];

drop field Date;

load ID,

D as Date,

if(D>=Date(DayStart(today())-365,'M/D/YYYY') and D<=Date(DayStart(today()),'M/D/YYYY'),'New','Old') as NewField

Resident Date;

Drop table Date;

[karan_kn]

Thanks for the response, what to do If the date field have blank or Null text?

[vishalarote]

First convert date as date format

Date(Date#(Date,'M/D/YYYY'),'M/D/YYYY')

after that use max function so it will neglect null values.

[big_dreams]

then what do you want ?

should count it as old or new??

Regards

[prachisangewar]

Hi,

Do you want to assign a default value to the Date if it is NULL or blank ?