Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Create a table between two times

Hello to all,

from a table made like this :

    

IDStartDateEndDateOra_inizioOra_fine
a02/03/2018 09:59:2302/03/2018 10:04:5309:5910:04
b02/03/2018 09:02:2302/03/2018 09:03:5309:0209:03
c02/03/2018 09:03:1302/03/2018 09:05:4309:0309:05

I would like to have a table like that:

   

IDStartDateEndDateOra_inizioOra_fine
a02/03/2018 09:59:2302/03/2018 10:04:5309:5910:04
a02/03/2018 09:59:2302/03/2018 10:04:5310:0010:04
a02/03/2018 09:59:2302/03/2018 10:04:5310:0110:04
a02/03/2018 09:59:2302/03/2018 10:04:5310:0210:04
a02/03/2018 09:59:2302/03/2018 10:04:5310:0310:04
a02/03/2018 09:59:2302/03/2018 10:04:5310:0410:04
b02/03/2018 09:02:2302/03/2018 09:03:5309:0209:03
b02/03/2018 09:02:2302/03/2018 09:03:5309:0309:03
c02/03/2018 09:03:1302/03/2018 09:05:4309:0309:05
c02/03/2018 09:03:1302/03/2018 09:05:4309:0409:05
c02/03/2018 09:03:1302/03/2018 09:05:4309:0509:05

Someone could help me.

Thanks in advance

3 Replies
Highlighted
Specialist II
Specialist II

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;

Highlighted
MVP
MVP

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;

Highlighted
Contributor II
Contributor II

Both solutions are fine


Thank you