I have a pax flow forecast per date and time. the range per date is from 06:00 hours till 22:00 hours. For everyday, the forecast can be per quarter. I noticed that some quarters are missing for some dates.
I would like to have a table where each date has alle the quarters in the range from 06:00 till 22:00. which means 60 quarters per date. I am looking for a way/the best way to get this in my date, perhaps in some sort of date calendar which can be joined with the other table containing the pax flow per filter.
I think I would need a script which generates for all dates present in the forecast table, a table with alle the quarters, from 06:00 till 22:00.
You can generate the hours and link them to the Forecast table dates using this code. However, it is not clear for me which forecast data do you want to display for that extra rows that you are adding.
Forecast: LOAD Distinct Period FROM [lib://DataFiles/Test sales.xlsx] (ooxml, embedded labels, table is Sheet1);
load Date, Time, 0 as MyAmount where not exists(Key, Date + Time); load *, time(1 / 24 / 60) + (iterno() * 15)) as Time; load date(StartDate + recno() -1) as Date autogenerate EndDate - StartDate;
which populates all missing quarter into the fact-table. The approach might be also reversed by creating at first the generic Date/Time combination and then joining/mapping the wanted information against it.
I have made this example refining the code provided before:
Forecast:
LOAD Date(Timestamp#(Period,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as Period,
Sales
INLINE [
Period, Sales
21/01/2026 06:00, 100
21/01/2026 06:45, 100
21/01/2026 08:30, 100
21/01/2026 10:00, 100
21/01/2026 10:15, 100
21/01/2026 11:45, 100
21/01/2026 12:45, 100
21/01/2026 13:00, 100
21/01/2026 14:15, 100
21/01/2026 15:30, 100
21/01/2026 17:15, 100
21/01/2026 19:30, 100
];
Forecast_temp:
LOAD Distinct
Date(Period,'DD/MM/YYYY') as Days
RESIDENT Forecast;
OUTER JOIN(Forecast_temp)
LOAD
Time( MakeTime(6,0) + (IterNo()-1) * (15/1440),'hh:mm' ) AS Time
AUTOGENERATE 1
WHILE MakeTime(6,0) + (IterNo()-1) * (15/1440) <= MakeTime(22,1);
Concatenate(Forecast)
LOAD Distinct
Date(Timestamp#(Days&' '&Time,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as Period,
'0' as Sales
RESIDENT Forecast_temp
WHERE NOT exists(Period,Date(Timestamp#(Days&' '&Time,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm'));
DROP TABLE Forecast_temp;
Theoretically, you just need to replace the first INLINE table with your fact data table. Ensure that the timestamps there have the proper format like I'm doing at the beginning in the INLINE. Otherwise, the joins and concatenations will fail.
Thanks, I've tried the concept. One thing I didn't mention in my post is that I have 8 stations in the list and each of them should have the dates/quarters for the whole day. I think a loop for the 8 different stations (which are in the column 'Filter' in the same source sheet as the date/quarter)?
I don't think a loop is needed, it is only needed to modify a bit the script to add the new field in the transformations. Let me know if the code below helps you (I only used 2 stations, but it works the same for 8 or other value):
Forecast:
LOAD Date(Timestamp#(Period,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as Period,
Sales,
Station,
Period&Station as Key
INLINE [
Period, Sales, Station
21/01/2026 06:00, 100, Station 1
21/01/2026 06:45, 100, Station 1
21/01/2026 08:30, 100, Station 1
21/01/2026 10:00, 100, Station 1
21/01/2026 10:15, 100, Station 1
21/01/2026 11:45, 100, Station 1
21/01/2026 12:45, 100, Station 1
21/01/2026 13:00, 100, Station 1
21/01/2026 14:15, 100, Station 1
21/01/2026 15:30, 100, Station 1
21/01/2026 17:15, 100, Station 1
21/01/2026 19:30, 100, Station 1
21/01/2026 06:00, 100, Station 2
21/01/2026 06:45, 100, Station 2
21/01/2026 08:30, 100, Station 2
21/01/2026 10:00, 100, Station 2
21/01/2026 10:15, 100, Station 2
21/01/2026 11:45, 100, Station 2
21/01/2026 12:45, 100, Station 2
21/01/2026 13:00, 100, Station 2
21/01/2026 14:15, 100, Station 2
21/01/2026 15:30, 100, Station 2
21/01/2026 17:15, 100, Station 2
21/01/2026 19:30, 100, Station 2
];
Forecast_temp:
LOAD Distinct
Date(Period,'DD/MM/YYYY') as Days,
Station
RESIDENT Forecast;
OUTER JOIN(Forecast_temp)
LOAD
Time( MakeTime(6,0) + (IterNo()-1) * (15/1440),'hh:mm' ) AS Time
AUTOGENERATE 1
WHILE MakeTime(6,0) + (IterNo()-1) * (15/1440) <= MakeTime(22,1);
Concatenate(Forecast)
LOAD Distinct
Date(Timestamp#(Days&' '&Time,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as Period,
'0' as Sales,
Station
RESIDENT Forecast_temp
WHERE NOT exists(Key,Date(Timestamp#(Days&' '&Time,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm')&Station);
Thanks. It's working. Gonna adapt it to my app/specific situation. Would love to be able to create such a solution myself but I guess it's just not my talent.
I can't get it working using my worksheet. I can follow the most steps, however, I don't understand this last line:
WHERE NOT exists(Key,Date(Timestamp#(Days&' '&Time,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm')&Station);
Is it possible to explain this line?
This is the adjusted script for my worksheet: //Laad combi van datum, tijd en filter als unieke sleutel [CAST_out_season (3)]: LOAD [Location description], [Filter], [Date], [Time], Date(Date,'DD-MM-YYYY') &' '& Time(Time,'hh:mm') as DateTime, [Forecast date], [Inflow], Date(Date,'DD-MM-YYYY') &' '& Time(Time,'hh:mm')&Filter as Key FROM [lib://selfservice_14_BI/SPL Drukteoverzicht/KMar doorkijk.xlsx] (ooxml, embedded labels, table is [CAST_out_season (3)]);
//tijdelijke tabel met unieke datums en filter: Forecast_temp: LOAD Distinct Date(Date) as Days, Filter RESIDENT [CAST_out_season (3)];
//samenvoegen met de tijdelijke tabel van de tijden die er moeten zijn van 06:00 - 22:00 met intervallen van 15 minuten: OUTER JOIN(Forecast_temp) LOAD Time( MakeTime(6,0) + (IterNo()-1) * (15/1440),'hh:mm' ) AS Time AUTOGENERATE 1 WHILE MakeTime(6,0) + (IterNo()-1) * (15/1440) <= MakeTime(22,1);
//samenvoegen van de tijdelijke tabel met de echte tabel, alleen voor unieke sleutels die niet bestaan Concatenate([CAST_out_season (3)]) LOAD Distinct Date(Timestamp#(Days&' '&Time,'DD-MM-YYYY hh:mm'),'DD-MM-YYYY hh:mm') as DateTime, '0' as Inflow, Filter RESIDENT Forecast_temp WHERE NOT exists(Key,Date(Timestamp#(Days&' '&Time,'DD-MM-YYYY hh:mm'),'DD-MM-YYYY hh:mm')&Filter);
DROP TABLE Forecast_temp; DROP FIELD Key;
I have a separate date and time field. I added explanation for each part of the script to understand what happens at that specific part. I do not get the desired result though.
And no matter what I try, I keep getting times like 00:00, 01:30 while this shouldn't be possible considered the script, is it?
I added the source excel sheet. Perhaps this could explain my issues getting the script working?
When I exit the script after the 'make time' part of the script, I see all the needed hours/quarters of an hour: //Laad combi van datum, tijd en filter als unieke sleutel [CAST_out_season (3)]: LOAD [Location description], [Filter], [Date], [Time], [Forecast date], [Inflow], Date(Date,'DD-MM-YYYY') &' '& Time(Time,'hh:mm') as DateTime, Date(Date,'DD-MM-YYYY') &' '& Time(Time,'hh:mm')&Filter as Key FROM [lib://selfservice_14_BI/SPL Drukteoverzicht/KMar doorkijk.xlsx] (ooxml, embedded labels, table is [CAST_out_season (3)]);
//tijdelijke tabel met uniek datums en filter Forecast_temp: LOAD Distinct Date(Date) as Days, Filter RESIDENT [CAST_out_season (3)];
//samenvoegen met de tijdelijke tabel van de tijden die er moeten zijn van 06:00 - 22:00 met intervallen van 15 minuten OUTER JOIN(Forecast_temp) //Temp_time: LOAD Time( MakeTime(6,0) + (IterNo()-1) * (15/1440),'hh:mm' ) AS Time AUTOGENERATE 1 WHILE MakeTime(6,0) + (IterNo()-1) * (15/1440) <= MakeTime(22,1);
But when the script continues, I end up with 00:00; 01:30; etcetera. I can't find the fix myself. I have been trying for hours now.
After trying and trying I somehow got it working and also discovered that my time window from 06:00 till 22:00 didn't work. So I changed it to cover the whole 24 hours and I found a way to deal with the separate date and time variables:
//Laad combi van datum, tijd en filter als unieke sleutel [CAST_out_season (3)]: LOAD [Location description], [Filter], //[Date], //[Time], [Forecast date], [Inflow], timestamp#(Date(Date,'DD-MM-YYYY') &' '& Time(Time,'hh:mm'),'DD-MM-YYYY hh:mm') as DateTime, Date(Date,'DD-MM-YYYY') &' '& Time(Time,'hh:mm')&Filter as Key FROM [lib://selfservice_14_BI/SPL Drukteoverzicht/KMar doorkijk.xlsx] (ooxml, embedded labels, table is [CAST_out_season (3)]);
//tijdelijke tabel met uniek datums en filter Forecast_temp: LOAD Distinct Date(DateTime, 'DD-MM-YYYY') as Days, Filter RESIDENT [CAST_out_season (3)];
//samenvoegen met de tijdelijke tabel van de tijden die er moeten zijn van 06:00 - 22:00 met intervallen van 15 minuten OUTER JOIN(Forecast_temp) //Temp_time: LOAD Time( MakeTime(0,0) + (IterNo()-1) * (15/1440),'hh:mm' ) AS Time AUTOGENERATE 1 WHILE MakeTime(0,0) + (IterNo()-1) * (15/1440) <= MakeTime(23,58);
//samenvoegen van de tijdelijke tabel met de echte tabel, alleen voor unieke sleutels die niet bestaan Concatenate([CAST_out_season (3)]) LOAD Distinct Date(Timestamp#(Days&' '&Time,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY hh:mm') as DateTime, //Days, //Time, '0' as Inflow, Filter RESIDENT Forecast_temp WHERE NOT exists(Key,Date(Timestamp#(Days&' '&Time,'DD-MM-YYYY hh:mm'),'DD-MM-YYYY hh:mm')&Filter);
DROP TABLE Forecast_temp; DROP FIELD Key;
Filter_Data: Load [Location description], [Filter], DateTime, timestamp#(date(DateTime, 'DD-MM-YYYY'),'DD-MM-YYYY') as Date, timestamp#(time(DateTime, 'hh:mm'),'DD-MM-YYYY') as Time, [Forecast date], [Inflow] Resident [CAST_out_season (3)];
drop table [CAST_out_season (3)];
Perhaps not the best or the most efficient way but for now ok.
I only need to add another variable which labels a specific time as early/late/night based on intervals. And find a way to nicely display date and time on the x-axis.
