topic Week off /leave Calculation in QlikView

Week off /leave Calculation

Anonymous — Sun, 21 Dec 2014 10:13:36 GMT

Hi All,

Please help me in finding the week off / leave for the dealers.

For example dealer A week off is on sunday as on 01-Jan- 2014 and again dealer is changing sunday to monday as week off from 07-Jan-2014.I need to have calendar marking sunday as leave from 01-Jan to 12 - Jan , since 07 is falling on tuesday i need to update monday as leave in the next week data

Sample Data :

Dealer, Date,WeekDay

A,01-Jan-2014,Sunday

A,07-Jan-2014,Monday

My output should be

Dealer	Date	WeekDay	DayCount	Week off
A	01-Jan-2014	Wednesday	1
A	02-Jan-2014	Thursday	1
A	03-Jan-2014	Friday	1
A	04-Jan-2014	Saturday	1
A	05-Jan-2014	Sunday	0	1
A	06-Jan-2014	Monday	1
A	07-Jan-2014	Tuesday	1
A	08-Jan-2014	Wednesday	1
A	09-Jan-2014	Thursday	1
A	10-Jan-2012	Friday	1
A	11-Jan-2014	Saturday	1
A	12-Jan-2014	Sunday	1
A	13-Jan-2014	Monday	0	1

Thanks in advance

Re: Week off /leave Calculation

preminqlik — Sun, 21 Dec 2014 11:18:43 GMT

hi there, hope this helps you

Data:

LOAD Dealer,Date(Date#(Date,'DD-MMM-YYYY')) as Date,WeekDay INLINE [

Dealer, Date,WeekDay

A,01-Jan-2014,Sunday

A,07-Jan-2014,Monday

];

DATASTAGE2:

LOAD

num(YearStart(today()))+IterNo()-1 AS Num,

Date(num(YearStart(today()))+IterNo()-1) AS Date

AUTOGENERATE 1 WHILE num(YearStart(today()))+IterNo()-1<= num(Yearend(today()));

Join

Load *

Resident Data;

drop table Data;

STAGE3:

Load *,

text(Date(Date,'WWWWWW')) as WEEKDAY,

if(text(Date(Date,'WWW'))=Capitalize(Trim(left(NewWeekDay,3))),0,1) as DAYCOUNT,

if(text(Date(Date,'WWW'))=Capitalize(Trim(left(NewWeekDay,3))),1,0) as WEEKOFF;

Load *,

if(len(Dealer)>0,Dealer,peek('DEALER')) as DEALER,

if(len(WeekDay)>0,WeekDay,peek('NewWeekDay')) as NewWeekDay

Resident DATASTAGE2 Order by Date asc;

DROP table DATASTAGE2;

EXIT Script;

Re: Week off /leave Calculation

its_anandrjs — Sun, 21 Dec 2014 13:12:46 GMT

Hi,

You can try this way

tmp1Data:
LOAD 
Num(Date(Date#(Date,'DD-MMM-YYYY'),'DD-MMM-YYYY')) as NumDate,
Date(Date#(Date,'DD-MMM-YYYY'),'DD-MMM-YYYY') as Date,
Dealer,WeekDay as WeekDay_Field;
LOAD * Inline
[
Dealer, Date,WeekDay
A,01-Jan-2014,Sunday
A,07-Jan-2014,Monday
];

LET vMinDate = Peek('NumDate',0,'tmp1Data');

Join

tmp2Data:
LOAD Distinct
Dealer,
Date($(vMinDate) + IterNo() -1,'DD-MMM-YYYY') as Date,
(Date($(vMinDate) + IterNo() -1,'WWWWWW')) as WeekDayNew
Resident tmp1Data
While  Num(Date(Date#(Date,'DD-MMM-YYYY'),'DD-MMM-YYYY')) + IterNo()-1 <= 
       Num(Date(Date#('31-Dec-2014','DD-MMM-YYYY'),'DD-MMM-YYYY'));
DROP Field NumDate;

Final:
NoConcatenate
LOAD *,
if(Flag=WeekDayNew,1,0) as WeekOff,
if(Flag=WeekDayNew,0,1) as DayCount;

LOAD *,
if(len(WeekDay_Field)>0,WeekDay_Field,Peek('Flag')) as Flag
Resident tmp1Data Order By Date asc;

DROP Table tmp1Data;

You can create the same calendar for Min date to Max date here i make it upto 31/12/2014

And as output you get

Regards

Anand

Re: Week off /leave Calculation

Anonymous — Mon, 22 Dec 2014 05:29:11 GMT

Hi,

Thanks to both of you for replying.

@Prem

Iam getting the correct answer as i expected , but if add another dealer its showing in correct result.

Its works perfect for single dealer

Example

Sample Data :

Dealer, Date,WeekDay

A,01-Jan-2014,Sunday

A,07-Jan-2014,Monday

B,31-Jan-2014,Friday

O/p should be

A - All dates from -1Jan - 31 Dec and week off data , sunday and monday

B- All dates from -1Jan - 31 Dec and week off data , friday

Thanks a lot

Re: Week off /leave Calculation

preminqlik — Mon, 22 Dec 2014 12:16:17 GMT

hi there,

try this (you can also do in subroutines using procedures but as per now try this )

Data:

LOAD Dealer,Date(Date#(Date,'DD-MMM-YYYY')) as Date,WeekDay INLINE [

Dealer, Date,WeekDay

A,01-Jan-2014,Sunday

A,07-Jan-2014,Monday

B,31-Jan-2014,Friday

];

DATASTAGE2:

LOAD

num(YearStart(today()))+IterNo()-1 AS Num,

Date(num(YearStart(today()))+IterNo()-1) AS Date

AUTOGENERATE 1 WHILE num(YearStart(today()))+IterNo()-1<= num(Yearend(today()));

Join

Load Distinct Dealer

Resident Data;

DATASTAGE3:

NoConcatenate

LOAD *

Resident DATASTAGE2;

Join

Load *

Resident Data;

drop table Data;

drop table DATASTAGE2;

STAGE3:

Load *,

text(Date(Date,'WWWWWW')) as WEEKDAY,

if(text(Date(Date,'WWW'))=Capitalize(Trim(left(NewWeekDay,3))),0,1) as DAYCOUNT,

if(text(Date(Date,'WWW'))=Capitalize(Trim(left(NewWeekDay,3))),1,0) as WEEKOFF;

Load *,

if(len(Dealer)>0,if(Peek(Dealer)=Dealer,if(len(WeekDay)>0,WeekDay,peek('NewWeekDay')),if(len(WeekDay)>0,WeekDay,'Sunday'))) as NewWeekDay

Resident DATASTAGE3 Order by Dealer,Date asc;

DROP table DATASTAGE3;

exit script;

Re: Week off /leave Calculation

Anonymous — Mon, 22 Dec 2014 12:57:40 GMT

Hi Prem,

Perfect

Thanks a lot this what iam looking for