Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
karan_kn
Creator II
Creator II

Create new field based on Date range

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:

date.JPG

1 Solution

Accepted Solutions
big_dreams
Creator III
Creator III

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,

View solution in original post

7 Replies
big_dreams
Creator III
Creator III

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

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
Partner - Creator II
Partner - Creator II

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;

Capture145.PNG

karan_kn
Creator II
Creator II
Author

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

vishalarote
Partner - Creator II
Partner - Creator II

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
Creator III
Creator III

then what do you want ?

should count it as old or new??

Regards

prachisangewar
Creator
Creator

Hi,

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