Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
QV Users,
I'am having issues with adjusting a timestamp. I have pulling in information on the arrival and completion time for customer emails. I have included a flow chart for the logic. I thought i would type it but the chart is so small and simple that I thought it would be best. I have a hoilday table inlcluded in my script and I hope to use it to skip over company holidays.
Any assistance would be much appreciated.
I think you should be able to reuse some of the logic I suggested here for a slightly different problem.
http://community.qlik.com/message/216532
I think you just need the part that correct the timestamp for the next business day (up to the Tickets table in the referenced example).
In case you can't open other's files:
// Create Calendar, start with non weekend or holiday
TMPCalendar:
LOAD
date(makedate(2012)+recno()-2) as Date
AutoGenerate 201;
// join Holiday calendar
left join LOAD * INLINE [
Date, Holiday
'01.01.2012', 'New Year'
'04.01.2012', Test
'06.01.2012', Epiphany
'06.04.2012', 'Good Friday'
'09.04.2012', 'Easter Monday'
'01.05.2012', '1st of May'
'17.05.2012', 'Holiday'
];
// Create some additional fields (date information and correction used later on)
TMPCalendar2:
LOAD *, WeekDay(Date) as Weekday, Month(Date) as Month, Year(Date) as Year;
Load *, rangesum(peek(Add1Day,rowno()-rangesum(Add1Day,1)),1,Add1Day) as Add2Days;
load *, if(weekday(peek(Date))>=5 or not isnull(peek(Holiday)), rangesum(peek(Add1Day),1)) as Add1Day
Resident TMPCalendar order by Date desc;
drop table TMPCalendar;
// Create final calendar, calculate correction for ticket creation (to add of creation is a weekend / holiday)
Calendar:
load *, if(Weekday >=5 or not isnull(Holiday), peek(Add1Day)) as CorrectStartDay
Resident TMPCalendar2 order by Date asc;
drop table TMPCalendar2;
// Create (or - in real setting - load) ticket creation timestamp, correct timestamp to next productive period, part I)
TMPTickets:
LOAD *, daystart(TicketCreationCorr) as TicketCreationCorrDate;
LOAD *, timestamp(if(hour(TicketCreation) < 9, daystart(TicketCreation)+interval#('09:00:00'), if(hour(TicketCreation)>16, daystart(TicketCreation)+1+Interval#('09:00:00'),TicketCreation))) as TicketCreationCorr;
LOAD Timestamp(makedate(2012)+RAND()*200) as TicketCreation
AutoGenerate 1000;
Left join LOAD Date as TicketCreationCorrDate, CorrectStartDay as TicketCorrectStartDay Resident Calendar;
// Create final ticket table: Correct timestamp to next productive period (add CorrectStartDay), part II
Tickets:
LOAD *, timestamp(rangesum(TicketCreationCorr,TicketCorrectStartDay)) as TicketProductiveStart, date(rangesum(TicketCreationCorrDate,TicketCorrectStartDay)) as TicketProductiveStartDate resident TMPTickets;
drop table TMPTickets;
Left join LOAD Date as TicketProductiveStartDate, Add1Day as TicketProductiveAdd1Day, Add2Days as TicketProductiveAdd2Days Resident Calendar;
// Calculate optimal end timestamp for productive periods, taking weekends / holidays into account
Result:
LOAD *, timestamp(rangesum(daystart(TicketProductiveStart)+interval#('09:00:00'),interval#('12:00:00')-(interval#('17:00:00')-frac(TicketProductiveStart)),
if(hour(TicketProductiveStart)>12,TicketProductiveAdd2Days-interval#('08:00:00'),TicketProductiveAdd1Day),1 )) as TicketProductiveEnd;
LOAD TicketProductiveStartDate as Date, TicketProductiveAdd1Day, TicketProductiveAdd2Days, TicketProductiveStart, TicketCreation Resident Tickets;
// drop unneeded fields and tables
drop table Tickets;
drop fields Add1Day, Add2Days, TicketProductiveAdd1Day, TicketProductiveAdd2Days,CorrectStartDay;
Hope this helps,
Stefan
I think you should be able to reuse some of the logic I suggested here for a slightly different problem.
http://community.qlik.com/message/216532
I think you just need the part that correct the timestamp for the next business day (up to the Tickets table in the referenced example).
In case you can't open other's files:
// Create Calendar, start with non weekend or holiday
TMPCalendar:
LOAD
date(makedate(2012)+recno()-2) as Date
AutoGenerate 201;
// join Holiday calendar
left join LOAD * INLINE [
Date, Holiday
'01.01.2012', 'New Year'
'04.01.2012', Test
'06.01.2012', Epiphany
'06.04.2012', 'Good Friday'
'09.04.2012', 'Easter Monday'
'01.05.2012', '1st of May'
'17.05.2012', 'Holiday'
];
// Create some additional fields (date information and correction used later on)
TMPCalendar2:
LOAD *, WeekDay(Date) as Weekday, Month(Date) as Month, Year(Date) as Year;
Load *, rangesum(peek(Add1Day,rowno()-rangesum(Add1Day,1)),1,Add1Day) as Add2Days;
load *, if(weekday(peek(Date))>=5 or not isnull(peek(Holiday)), rangesum(peek(Add1Day),1)) as Add1Day
Resident TMPCalendar order by Date desc;
drop table TMPCalendar;
// Create final calendar, calculate correction for ticket creation (to add of creation is a weekend / holiday)
Calendar:
load *, if(Weekday >=5 or not isnull(Holiday), peek(Add1Day)) as CorrectStartDay
Resident TMPCalendar2 order by Date asc;
drop table TMPCalendar2;
// Create (or - in real setting - load) ticket creation timestamp, correct timestamp to next productive period, part I)
TMPTickets:
LOAD *, daystart(TicketCreationCorr) as TicketCreationCorrDate;
LOAD *, timestamp(if(hour(TicketCreation) < 9, daystart(TicketCreation)+interval#('09:00:00'), if(hour(TicketCreation)>16, daystart(TicketCreation)+1+Interval#('09:00:00'),TicketCreation))) as TicketCreationCorr;
LOAD Timestamp(makedate(2012)+RAND()*200) as TicketCreation
AutoGenerate 1000;
Left join LOAD Date as TicketCreationCorrDate, CorrectStartDay as TicketCorrectStartDay Resident Calendar;
// Create final ticket table: Correct timestamp to next productive period (add CorrectStartDay), part II
Tickets:
LOAD *, timestamp(rangesum(TicketCreationCorr,TicketCorrectStartDay)) as TicketProductiveStart, date(rangesum(TicketCreationCorrDate,TicketCorrectStartDay)) as TicketProductiveStartDate resident TMPTickets;
drop table TMPTickets;
Left join LOAD Date as TicketProductiveStartDate, Add1Day as TicketProductiveAdd1Day, Add2Days as TicketProductiveAdd2Days Resident Calendar;
// Calculate optimal end timestamp for productive periods, taking weekends / holidays into account
Result:
LOAD *, timestamp(rangesum(daystart(TicketProductiveStart)+interval#('09:00:00'),interval#('12:00:00')-(interval#('17:00:00')-frac(TicketProductiveStart)),
if(hour(TicketProductiveStart)>12,TicketProductiveAdd2Days-interval#('08:00:00'),TicketProductiveAdd1Day),1 )) as TicketProductiveEnd;
LOAD TicketProductiveStartDate as Date, TicketProductiveAdd1Day, TicketProductiveAdd2Days, TicketProductiveStart, TicketCreation Resident Tickets;
// drop unneeded fields and tables
drop table Tickets;
drop fields Add1Day, Add2Days, TicketProductiveAdd1Day, TicketProductiveAdd2Days,CorrectStartDay;
Hope this helps,
Stefan
Please view this link as well,
It has almost same problem being faced.
http://community.qlik.com/message/222932#222932
Regards
Pefect! Its works! Thank you.