topic Re: Period in QlikView

Period

upaliwije — Sun, 29 Oct 2017 03:01:35 GMT

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

Re: Period

upaliwije — Sun, 29 Oct 2017 03:52:05 GMT

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,

Re: Period

vishsaggi — Sun, 29 Oct 2017 04:20:19 GMT

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

];

Re: Period

upaliwije — Sun, 29 Oct 2017 04:28:50 GMT

Thanks

I will try!

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

Re: Period

upaliwije — Sun, 29 Oct 2017 04:58:52 GMT

Dear Viswa

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

Re: Period

upaliwije — Sun, 29 Oct 2017 05:33:35 GMT

Could some one help me please

Re: Period

HirisH_V7 — Sun, 29 Oct 2017 06:52:10 GMT

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

Re: Period

HirisH_V7 — Sun, 29 Oct 2017 07:03:52 GMT

I don't think you should consider leap year .

Re: Period

mdmukramali — Sun, 29 Oct 2017 07:14:50 GMT

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

]

;

Re: Period

antoniotiman — Sun, 29 Oct 2017 09:41:19 GMT

Upali,

all RISK_DATE are 3rd Period.

Wich Period is 12-May-15 ?

Re: Period

upaliwije — Sun, 29 Oct 2017 09:53:06 GMT

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

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

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

Re: Period

vishsaggi — Sun, 29 Oct 2017 16:18:48 GMT

Try this?

Period:

LOAD *,

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

IF(Period2 >= '15-Aug' AND Period2 <= '30-Oct', '2nd')) 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

];

Concatenate

LOAD RISK_DATE, Period2, IF(Period2 <= '30-Oct', '3rd') AS PeriodStatus

Resident Period;

Re: Period

HirisH_V7 — Sun, 29 Oct 2017 16:47:51 GMT

Hi upaliwije

Did you try above

-Hirish

Re: Period

vishsaggi — Mon, 30 Oct 2017 13:50:20 GMT

I could not see Period 3rd when i used Mohammed script. Did the requirement changed? Just wondering to know!

Re: Period

mdmukramali — Mon, 30 Oct 2017 14:08:46 GMT

Hi,

In The Sample Data, all the RISK_DATES are less than 30-Oct,

So that is the reason no RISK_DATES under period 3rd.

Re: Period

vishsaggi — Mon, 30 Oct 2017 14:16:05 GMT

That is what he mentioned his 3rd Period should be between 1st Jan and 30 Oct.

His previous reply

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

So got confused. Cool thanks.

Re: Period

upaliwije — Mon, 30 Oct 2017 16:00:35 GMT

Requirement did not change. But I got total 1st+2nd as 3rd Period