Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to convert periods on work days

Hi,

I´ve been seeing several examples, by my Qlikview skills aren´t good enough yet. I would like to convert on load script this:

ID da TarefaInício do períodoFim do período
2428/4/14 15:3130/4/14 08:43
1928/4/14 09:2528/4/14 15:30
525/4/14 11:2525/4/14 12:39
525/4/14 13:1225/4/14 18:03

On this:

ID da TarefaStartEnd
2428/4/14 15:3128/04/14 18:00
2429/04/14 09:0029/04/14 18:00
2429/04/14 09:0030/4/14 08:43
1928/4/14 09:2528/4/14 15:30
525/4/14 11:2525/4/14 12:39
525/4/14 13:1225/4/14 18:03

Which means I need to show a time period (example ID #24) in work hours day by day. I need to do this on load script, because I need to use those dates after.

I really appreciated any help.

Fernando

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: How to convert periods on work days

Hi,

Try this script

Data:

LOAD [ID da Tarefa],

     TimeStamp(Timestamp#([Início do período], 'DD/M/YY hh:mm'), 'DD/M/YY hh:mm') AS [Início do período],

     TimeStamp(Timestamp#([Fim do período], 'DD/M/YY hh:mm'), 'DD/M/YY hh:mm') AS [Fim do período]

FROM

[http://community.qlik.com/thread/117938]

(html, codepage is 1252, embedded labels, table is @1);

LEFT JOIN(Data)

LOAD

[ID da Tarefa],

TimeStamp(Min([Início do período]), 'DD/M/YY hh:mm') As MinStartDate,

TimeStamp(Max([Fim do período]), 'DD/M/YY hh:mm') AS MaxStartDate

RESIDENT Data

GROUP BY [ID da Tarefa];

DataFinal:

LOAD

[ID da Tarefa],

If(IterNo() > 1, TimeStamp(Floor(TimeStamp#(MinStartDate, 'DD/M/YY hh:mm') + IterNo() - 1)  + (9/24), 'DD/M/YY hh:mm'), MinStartDate)   AS Start,

If(Ceil(MaxStartDate - MinStartDate) = 1, MaxStartDate, 

  If(IterNo() > 1 AND Ceil(MinStartDate + Iterno() - 1) = Ceil(MaxStartDate),

  MaxStartDate,

  TimeStamp(Floor(TimeStamp#(MinStartDate, 'DD/M/YY hh:mm') + IterNo() - 1)  + (18/24), 'DD/M/YY hh:mm'))) AS End

Resident Data

WHILE Ceil(MinStartDate + Iterno() - 1) <= Ceil(MaxStartDate);

DROP TABLE Data;

Regards,

jagan.

4 Replies
Not applicable

Re: How to convert periods on work days

Hi Ferrnando , I didn't understand your time breakdown for id 24. what is the exact time period for each day

Not applicable

Re: How to convert periods on work days

If the day time window is 6AM to 6PM, Please find the attached file for reference.

MVP
MVP

Re: How to convert periods on work days

Hi,

Try this script

Data:

LOAD [ID da Tarefa],

     TimeStamp(Timestamp#([Início do período], 'DD/M/YY hh:mm'), 'DD/M/YY hh:mm') AS [Início do período],

     TimeStamp(Timestamp#([Fim do período], 'DD/M/YY hh:mm'), 'DD/M/YY hh:mm') AS [Fim do período]

FROM

[http://community.qlik.com/thread/117938]

(html, codepage is 1252, embedded labels, table is @1);

LEFT JOIN(Data)

LOAD

[ID da Tarefa],

TimeStamp(Min([Início do período]), 'DD/M/YY hh:mm') As MinStartDate,

TimeStamp(Max([Fim do período]), 'DD/M/YY hh:mm') AS MaxStartDate

RESIDENT Data

GROUP BY [ID da Tarefa];

DataFinal:

LOAD

[ID da Tarefa],

If(IterNo() > 1, TimeStamp(Floor(TimeStamp#(MinStartDate, 'DD/M/YY hh:mm') + IterNo() - 1)  + (9/24), 'DD/M/YY hh:mm'), MinStartDate)   AS Start,

If(Ceil(MaxStartDate - MinStartDate) = 1, MaxStartDate, 

  If(IterNo() > 1 AND Ceil(MinStartDate + Iterno() - 1) = Ceil(MaxStartDate),

  MaxStartDate,

  TimeStamp(Floor(TimeStamp#(MinStartDate, 'DD/M/YY hh:mm') + IterNo() - 1)  + (18/24), 'DD/M/YY hh:mm'))) AS End

Resident Data

WHILE Ceil(MinStartDate + Iterno() - 1) <= Ceil(MaxStartDate);

DROP TABLE Data;

Regards,

jagan.

Not applicable

Re: How to convert periods on work days

I really appreciated Jagan

Community Browser