topic Re: Create a table between two times in QlikView

Create a table between two times

galileumax — Sat, 10 Mar 2018 18:57:49 GMT

Hello to all,

from a table made like this :

ID	StartDate	EndDate	Ora_inizio	Ora_fine
a	02/03/2018 09:59:23	02/03/2018 10:04:53	09:59	10:04
b	02/03/2018 09:02:23	02/03/2018 09:03:53	09:02	09:03
c	02/03/2018 09:03:13	02/03/2018 09:05:43	09:03	09:05

I would like to have a table like that:

ID	StartDate	EndDate	Ora_inizio	Ora_fine
a	02/03/2018 09:59:23	02/03/2018 10:04:53	09:59	10:04
a	02/03/2018 09:59:23	02/03/2018 10:04:53	10:00	10:04
a	02/03/2018 09:59:23	02/03/2018 10:04:53	10:01	10:04
a	02/03/2018 09:59:23	02/03/2018 10:04:53	10:02	10:04
a	02/03/2018 09:59:23	02/03/2018 10:04:53	10:03	10:04
a	02/03/2018 09:59:23	02/03/2018 10:04:53	10:04	10:04
b	02/03/2018 09:02:23	02/03/2018 09:03:53	09:02	09:03
b	02/03/2018 09:02:23	02/03/2018 09:03:53	09:03	09:03
c	02/03/2018 09:03:13	02/03/2018 09:05:43	09:03	09:05
c	02/03/2018 09:03:13	02/03/2018 09:05:43	09:04	09:05
c	02/03/2018 09:03:13	02/03/2018 09:05:43	09:05	09:05

Someone could help me.

Thanks in advance

Re: Create a table between two times

jwjackso — Sat, 10 Mar 2018 22:34:50 GMT

After you load you table, try this:

Concatenate

Load ID,

StartDate,

EndDate,

Time(Num(Time#(Ora_inizio,'HH:mm')) + (IterNo()/(24*60)),'HH:mm') as Ora_inizio,

Ora_fine

Resident YourTableName While IterNo() <= ((Num(Time#(Ora_fine,'HH:mm')) - Num(Time#(Ora_inizio,'HH:mm'))) * 24*60) + 1;

Re: Create a table between two times

MK_QSL — Sat, 10 Mar 2018 22:42:47 GMT

something like below..

Temp:
Load 
 ID,
 TimeStamp#(StartDate,'DD/MM/YYYY hh:mm:ss') as StartDate,
 TimeStamp#(EndDate,'DD/MM/YYYY hh:mm:ss') as EndDate,
 Time#(Ora_inizio,'hh:mm') as Ora_inizio,
 Time#(Ora_fine,'hh:mm') as Ora_fine
Inline
[
 ID, StartDate, EndDate, Ora_inizio, Ora_fine
 a, 02/03/2018 09:59:23, 02/03/2018 10:04:53, 09:59, 10:04
 b, 02/03/2018 09:02:23, 02/03/2018 09:03:53, 09:02, 09:03
 c, 02/03/2018 09:03:13, 02/03/2018 09:05:43, 09:03, 09:05
];


NoConcatenate
Final:
Load 
 ID, StartDate, EndDate, 
 Time(Ora_inizio + Time((IterNo()-1)/24/60),'hh:mm') as Ora_inizio,
 Ora_fine
Resident Temp
While Time(Ora_inizio + Time((IterNo()-1)/24/60),'hh:mm') <= Ora_fine
;


Drop Table Temp;

Re: Create a table between two times

galileumax — Tue, 13 Mar 2018 09:43:35 GMT

Both solutions are fine

Thank you