Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
]
;
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,
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
];
Thanks
I will try!
My 3rd 3rd Period is all date between 1st-Jan to 30th Oct
Dear Viswa
I get wrong data as shown below, In some cases blank period status and period2 appear pls help
Could some one help me please
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
I don't think you should consider leap year .
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
]
;
Upali,
all RISK_DATE are 3rd Period.
Wich Period is 12-May-15 ?