Solved: Adjusting Timestamp to Next Work Day - Qlik Community

villegasi03 · ‎2012-05-25

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.

swuehl · ‎2012-05-26

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

View solution in original post

swuehl · ‎2012-05-26