Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

4 Replies
Not applicable
Author

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

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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
Author

I really appreciated Jagan