topic Re: Create one record for each date between two dates in QlikView

Create one record for each date between two dates

Tue, 24 Nov 2015 03:54:13 GMT

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
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.

Re: Create one record for each date between two dates

jonathandienst — Tue, 24 Nov 2015 05:56:54 GMT

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.

Re: Create one record for each date between two dates

Tue, 24 Nov 2015 06:08:26 GMT

Awesome, thank you very much. That worked perfectly.

Re: Create one record for each date between two dates

jagan — Tue, 24 Nov 2015 06:10:47 GMT

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.

Re: Create one record for each date between two dates

dena_reavis — Tue, 07 Apr 2020 20:51:53 GMT

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

Re: Create one record for each date between two dates

dena_reavis — Wed, 08 Apr 2020 12:18:02 GMT

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

Re: Create one record for each date between two dates

samim — Mon, 28 Nov 2022 13:27:50 GMT

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.