Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 Tarefa | Início do período | Fim do período |
24 | 28/4/14 15:31 | 30/4/14 08:43 |
19 | 28/4/14 09:25 | 28/4/14 15:30 |
5 | 25/4/14 11:25 | 25/4/14 12:39 |
5 | 25/4/14 13:12 | 25/4/14 18:03 |
On this:
ID da Tarefa | Start | End |
24 | 28/4/14 15:31 | 28/04/14 18:00 |
24 | 29/04/14 09:00 | 29/04/14 18:00 |
24 | 29/04/14 09:00 | 30/4/14 08:43 |
19 | 28/4/14 09:25 | 28/4/14 15:30 |
5 | 25/4/14 11:25 | 25/4/14 12:39 |
5 | 25/4/14 13:12 | 25/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
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.
Hi Ferrnando , I didn't understand your time breakdown for id 24. what is the exact time period for each day
If the day time window is 6AM to 6PM, Please find the attached file for reference.
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.
I really appreciated Jagan