Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Creator III
Creator III

Re: Create new field based on Date range

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

Re: Create new field based on Date range

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

Highlighted
Creator
Creator

Re: Create new field based on Date range

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

];

Highlighted
Partner
Partner

Re: Create new field based on Date range

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

Highlighted
Creator II
Creator II

Re: Create new field based on Date range

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

Highlighted
Partner
Partner

Re: Create new field based on Date range

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.

Creator III
Creator III

Re: Create new field based on Date range

then what do you want ?

should count it as old or new??

Regards

Highlighted
Creator
Creator

Re: Create new field based on Date range

Hi,

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