topic Re: Create new field based on Date range in QlikView

Create new field based on Date range

karan_kn — Wed, 25 Nov 2020 16:16:04 GMT

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:

Re: Create new field based on Date range

big_dreams — Tue, 15 May 2018 10:17:15 GMT

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,

Re: Create new field based on Date range

prachisangewar — Tue, 15 May 2018 10:33:50 GMT

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

];

Re: Create new field based on Date range

vishalarote — Tue, 15 May 2018 10:42:24 GMT

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;

Re: Create new field based on Date range

karan_kn — Tue, 15 May 2018 11:49:02 GMT

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

Re: Create new field based on Date range

vishalarote — Tue, 15 May 2018 11:54:18 GMT

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.

Re: Create new field based on Date range

big_dreams — Tue, 15 May 2018 11:54:32 GMT

then what do you want ?

should count it as old or new??

Regards

Re: Create new field based on Date range

prachisangewar — Thu, 17 May 2018 09:37:00 GMT

Hi,

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