Period

[upaliwije]

Hi Friends

In my data table I have following dates

RISK_DATE

01-Jan-15

02-Jan-16

12-May-15

13-May-16

10-Aug-17

11-Aug-15

13-Aug-16

14-Aug-15

17-Aug-15

18-Aug-17

23-Oct-15

24-Oct-16

27-Oct-17

In my load script I want to create 3 periods

1st  RISK&MONTH<='14-AUG'

2nd RISK&MONTH>='15-AUG' and RISK&MONTH<='30-OCT'

3rd RISK&MONTH<='30-OCT'

Pls help me to write this in data load script so that I will have 3 dimensions for periods

[upaliwije]

I have tried following syntax but it does not work

IF(DAY(RISK_DATE) &'-'& MONTH(RISK_DATE)>='01-JAN' AND DAY(RISK_DATE) &'-'& MONTH(RISK_DATE)<='14-AUG',1st',IF(DAY(RISK_DATE) &'-'& MONTH(RISK_DATE)>='15-AUG' AND DAY(RISK_DATE) &'-'& MONTH(RISK_DATE)<='31-OCT' ,'2nd',IF(DAY(RISK_DATE) &'-'& MONTH(RISK_DATE)>='01-JAN' AND DAY(RISK_DATE) &'-'& MONTH(RISK_DATE)<='30-OCT' ,'2nd')))as PERIOD,

[vishsaggi]

What should be the dates for your 3rd RISKMonth which is like <= 30 you want all the dates to be in that 3rd. Or is it something else like >= 30?

May be try like below:

I think this is what you meant.

LOAD *, IF(Period2 <= '14-Aug', '1st',

        IF(Period2 >= '15-Aug' AND Period2 < '30-Oct', '2nd',

        IF(Period2 >= '30-Oct', '3rd'))) AS PeriodStatus;

LOAD *, Date(Date#(RISK_DATE, 'DD-MMM-YY'), 'DD-MMM') AS Period2 INLINE [

RISK_DATE

01-Jan-15

02-Jan-16

12-May-15

13-May-16

10-Aug-17

11-Aug-15

13-Aug-16

14-Aug-15

17-Aug-15

18-Aug-17

23-Oct-15

24-Oct-16

27-Oct-17

];

[upaliwije]

Thanks

I will try!

My 3rd 3rd Period is all date between 1st-Jan to  30th Oct

[upaliwije]

Dear Viswa

I get wrong data as shown below, In some cases blank period status and period2  appear pls help

[upaliwije]

Could some one help me please

[HirisH_V7]

Hi ,

Check this out, Using DayNumberOfYear function,

Data:
LOAD *,
IF(DayNum>='1' AND DayNum<='227','1st',
IF(DayNum>='228' AND DayNum<'304' ,'2nd',
IF(DayNum>='304','3rd')))as PERIOD;
LOAD *,
DayNumberOfYear(Date) as DayNum
INLINE [
Date
01-Jan-15
02-Jan-16
12-May-15
13-May-16
10-Aug-17
11-Aug-15
13-Aug-16
14-Aug-15
15-Aug-15
17-Aug-15
18-Aug-17
23-Oct-15
30-Oct-15
24-Oct-16
27-Oct-17
01-Nov-17
];

HTH,

PFA,

Hirish.V

[HirisH_V7]

I don't think you should consider leap year .

[mdmukramali · Accepted solution]

Hi,

Can you try below script;

LOAD *,

if(Date(Date#(RISK_DATE,'DD-MMM-YY'),'DD-MM-YYYY')<=Date(MakeDate(Year(Date#(RISK_DATE,'DD-MMM-YY')),8,14),'DD-MM-YYYY'),'1st',

if(Date(Date#(RISK_DATE,'DD-MMM-YY'),'DD-MM-YYYY')>Date(MakeDate(Year(Date#(RISK_DATE,'DD-MMM-YY')),8,15),'DD-MM-YYYY')

    and Date(Date#(RISK_DATE,'DD-MMM-YY'),'DD-MM-YYYY')<=Date(MakeDate(Year(Date#(RISK_DATE,'DD-MMM-YY')),10,30),'DD-MM-YYYY'),'2nd'

,if(Date(Date#(RISK_DATE,'DD-MMM-YY'),'DD-MM-YYYY')>Date(MakeDate(Year(Date#(RISK_DATE,'DD-MMM-YY')),10,30),'DD-MM-YYYY'),'3rd'))) as PERIOD

Inline

[

RISK_DATE

01-Jan-15

02-Jan-16

12-May-15

13-May-16

10-Aug-17

11-Aug-15

13-Aug-16

14-Aug-15

17-Aug-15

18-Aug-17

23-Oct-15

24-Oct-16

27-Oct-17

]

;

[antoniotiman]

Upali,

all RISK_DATE are 3rd Period.

Wich Period is 12-May-15 ?