Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
EXIT Script;
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
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
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;
Hi Prem,
Perfect
Thanks a lot this what iam looking for