Create one record for each date between two dates

[#1]

Hi

I'm trying to visualize the actual useage of a machine on a calendar.

I have a table with the events (fligths). This table holds all the flights (completed or scheduled) and also reservations for given periods. This table as a Start and End Date (in DateTime format). Besides each event, I have the type of event (four possible types: Reserved, Scheduled, Completed, Maintenance).

Aircraft	Date Start	Date End	Type	etc.
A	8.12.15 10:00	11.12.15 17:00	Maintenance
A	1.12.15 14:27	1.12.15 17:29	Completed
B	30.11.15 04:25	30.11.15 8:50	Completed
A	20.12.15 22:45	21.12.15 02:30	Reserved

Now I would like to create a table that has one record per aircraft, date and type. So if aircraft A has a maintenance event from 8.12. to 11.12., I would like to have the records like this:

Date	Aircraft	Type
8.12.	A	Maintenance
9.12.	A	Maintenance
10.12.	A	Maintenance
11.12.	A	Maintenance
20.12.	A	Reserved
21.12.	A	Reserved
30.11.	B	Completed

I tried the IntervalMatch, but with no success.

How can I load the data to have a record for each day?

Thanks in advance.

[jonathandienst · Accepted solution]

You can do this with a preceding load with an iterator:

LOAD

  Aircraft,

  Type,

  Date(Start + IterNo() - 1) As Date

While Start + IterNo() - 1 <= End

;

LOAD Aircraft,

  Date(Floor(Timestamp#(Start, 'd.MM.yyyy hh:mm'))) As Start,

  Date(Floor(Timestamp#(End, 'd.MM.yyyy hh:mm'))) As End,

  Type

Inline

[

  Aircraft, Start, End, Type

  A, 8.12.15 10:00, 11.12.15 17:00, Maintenance

  A, 1.12.15 14:27, 1.12.15 17:29, Completed

  B, 30.11.15 04:25, 30.11.15 8:50, Completed

  A, 20.12.15 22:45, 21.12.15 02:30, Reserved

];

Replace the italic portion with your actual load.

[#3]

Awesome, thank you very much. That worked perfectly.

[jagan]

Hi,

You can try this by using WHILE like below

Data:

LOAD

  Aircraft,

  Type,

  Date(Start + IterNo() - 1) As Date

While Start + IterNo() - 1 <= End

;

LOAD Aircraft,

  Date(Floor(Timestamp#(Start, 'D.MM.YY hh:mm'))) As Start,

  Date(Floor(Timestamp#(End, 'D.MM.YY hh:mm'))) As End,

  Type

Inline

[

  Aircraft, Start, End, Type

  A, 8.12.15 10:00, 11.12.15 17:00, Maintenance

  A, 1.12.15 14:27, 1.12.15 17:29, Completed

  B, 30.11.15 04:25, 30.11.15 8:50, Completed

  A, 20.12.15 22:45, 21.12.15 02:30, Reserved

];

Hope this helps you.

Regards,

Jagan.

[dena_reavis]

Hi, 

Thank you from 2020! 

How might I alter this to get one row for each Month between the start and end dates? I have time periods that are 3 to 12 months and I need to end up with one row for each month. 

Thank you

[dena_reavis]

I tried a few things and thanks to this post and one by @rwunderlich  on getting the number of months between two dates ,

I was able to work this out, solving my issue where deliveries may be contracted as "quantities to be delivered" over the few months between the start and end dates. For analysis purposes I need to assume the full quantity would fall evenly over the months :

LOAD
Aircraft,
Quantity as Full_Quantity,
Quantity/(Months_Between+1) as Monthly_Quantity,
Months_Between+1 as Months_Between,
Start,
End,
IterNo() as Iteration,
Date(AddMonths(Start, IterNo() - 1)) As Delivery_Month
While MonthStart + IterNo() - 1 <= MonthEnd
;

LOAD Aircraft,
((year(Start)*12)+month(Start)) As MonthStart,
((year(End)*12)+month(End)) As MonthEnd,
Start,
End,
((year(End)*12)+month(End)) - ((year(Start)*12)+month(Start)) as Months_Between,
Quantity
Inline
[
Aircraft, Start, End, Quantity
A, 04/01/2020, 06/30/2020, 500
A, 04/01/2020, 05/30/2020, 600
B, 04/01/2020, 06/30/2020, 900
A, 04/01/2020, 07/30/2020, 1200

];

The Qlik Community is the best! Thank you

[samim]

Is there a possibility to have the same result per hour of the day? i'm trying to count  all the people that has been inside a place. My tabel structure is same as above, but switch aircraft and type with person and place.