Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a file that contains planned deliveries for a construction project
The file looks like this:
// Project deliveries
LOAD
ShipmentNumber as CPShipmentNumber,
CheckPoint as CPCheckPoint,
Description as CPDescription,
NumberOfVehicles as CPNumberOfVehicles,
UnloadingZone as CPUnloadingZone,
Date(PlanStartDate,'YY-MM-DD') as CPPlanStartDate,
Time(PlanStartTime,'hh.mm') as CPPlanStartTime,
Date(PlanEndDate,'YY-MM-DD') as CPPlanEndDate,
Time(PlanEndTime,'hh.mm') as CPPlanEndTime,
if(Hour(PlanStartTime)>0, Hour(PlanStartTime)&'-'&(Hour(PlanStartTime)+1)) as CPPlanStartHour,
SQL SELECT *FROM lsp.LOShipmentTasks where Len('CheckPoint')>1 and NumberOfVehicles>0;
My problem is when there is a delivery with several vehicles (NumberofVehicles>1) I want to distribute these vehicles evenly over the time between planned start time and planned finish time.
I imagine that in qlik sense sqript I would like to create a new file that contains all the records in the existing file (lsp.LOShipmentTask) but where the records with several vehicles (NumberofVehicles>1) are evenly distributed over time on new records.
So my question is how do I create this new table based on the data I have in the existing file lsp.LOShipmentTask.
Ah yes, bad copy and paste.
I created this script with 2 rows of simple dummy data that first gets loaded:
Raw:
Load * Inline [
CPShipmentNumber,CPPlanStartDate,CPPlanStartTime,CPPlanEndDate,CPPlanEndTime,CPNumberOfVehicles
50,8/12/2022,08.00,8/12/2022,12.00,2
40,8/12/2022,07.00,8/12/2022,14.00,3
];
/*Then we combine the date and time to create timestamps that Qlik can properly add/subtract, and then we use IterNo to get as many rows as there are vehicles: */
Parsed:
Load *,
((CPPlanEnd-CPPlanStart)/CPNumberOfVehicles) as CPTimePerVehicle,
IterNo() as CPVehicle#
While IterNo()<=CPNumberOfVehicles;
Load
CPShipmentNumber,
TimeStamp(CPPlanStartDate + Time#(CPPlanStartTime, 'hh.mm')) as CPPlanStart,
TimeStamp(CPPlanEndDate + Time#(CPPlanEndTime, 'hh.mm')) as CPPlanEnd,
CPNumberOfVehicles
Resident Raw
Where CPNumberOfVehicles>1;
/*Then we calculate the equal portions of time for each vehicle, and we use Peek to compare each row with the previous row. If it's the same shipment number, then we take the previous end time as the start time, and we add time to the start to get the new end time. */
Final:
Load *,
CPTimePerVehicle*[CPVehicle#] as TimeToEnd,
If(Peek(CPShipmentNumber)=CPShipmentNumber, Time(Peek(CPNewEndTime),'hh.mm'), Time(CPPlanStart, 'hh.mm')) as CPNewStartTime,
If(Peek(CPShipmentNumber)=CPShipmentNumber, Time(Peek(CPPlanStart)+(CPTimePerVehicle*[CPVehicle#]),'hh.mm'), Time(CPPlanStart+CPTimePerVehicle, 'hh.mm')) as CPNewEndTime
Resident Parsed
Order By CPShipmentNumber, [CPVehicle#];
//Drop the temporary tables:
Drop Tables Raw, Parsed;
Would this be a good example in your first table?
CPShipmentNumber | CPPlanStartTime | CPPlanStartTime | CPNumberOfVehicles |
50 | 08.00 | 12.00 | 2 |
From this do you want to create a new table with two rows, like this?
CPShipmentNumber | CPPlanStartTime | CPPlanStartTime | CPNumberOfVehicles |
50 | 08.00 | 10.00 | 1 |
50 | 10.00 | 12.00 | 1 |
Yes, That was exactly what I meant! 😊 (Except that the third column should be CPPlanEndTime)
CPShipmentNumber |
CPPlanStartTime |
CPPlanEndTime |
CPNumberOfVehicles |
50 |
08.00 |
10.00 |
1 |
50 |
10.00 |
12.00 |
1 |
Thanks for your help
// Magnus
Ah yes, bad copy and paste.
I created this script with 2 rows of simple dummy data that first gets loaded:
Raw:
Load * Inline [
CPShipmentNumber,CPPlanStartDate,CPPlanStartTime,CPPlanEndDate,CPPlanEndTime,CPNumberOfVehicles
50,8/12/2022,08.00,8/12/2022,12.00,2
40,8/12/2022,07.00,8/12/2022,14.00,3
];
/*Then we combine the date and time to create timestamps that Qlik can properly add/subtract, and then we use IterNo to get as many rows as there are vehicles: */
Parsed:
Load *,
((CPPlanEnd-CPPlanStart)/CPNumberOfVehicles) as CPTimePerVehicle,
IterNo() as CPVehicle#
While IterNo()<=CPNumberOfVehicles;
Load
CPShipmentNumber,
TimeStamp(CPPlanStartDate + Time#(CPPlanStartTime, 'hh.mm')) as CPPlanStart,
TimeStamp(CPPlanEndDate + Time#(CPPlanEndTime, 'hh.mm')) as CPPlanEnd,
CPNumberOfVehicles
Resident Raw
Where CPNumberOfVehicles>1;
/*Then we calculate the equal portions of time for each vehicle, and we use Peek to compare each row with the previous row. If it's the same shipment number, then we take the previous end time as the start time, and we add time to the start to get the new end time. */
Final:
Load *,
CPTimePerVehicle*[CPVehicle#] as TimeToEnd,
If(Peek(CPShipmentNumber)=CPShipmentNumber, Time(Peek(CPNewEndTime),'hh.mm'), Time(CPPlanStart, 'hh.mm')) as CPNewStartTime,
If(Peek(CPShipmentNumber)=CPShipmentNumber, Time(Peek(CPPlanStart)+(CPTimePerVehicle*[CPVehicle#]),'hh.mm'), Time(CPPlanStart+CPTimePerVehicle, 'hh.mm')) as CPNewEndTime
Resident Parsed
Order By CPShipmentNumber, [CPVehicle#];
//Drop the temporary tables:
Drop Tables Raw, Parsed;
Thank you so much Lauri your solution works perfectly!😀
However, I have an additional problem and that is when I use fields from a joined file to my primary table (RAW).
Instead of using the fields for planned start dates for the delivery, I want to use the actual check-in date for the delivery and do the distribution over time. The Checkin Date is located in the Joined file (LOShipment). I want to do the same calculation based on the actual checkindate and time as for the planned date and time.
My problem is it seems that I got multiple records when i use fields from the joined file in the parsed and final file.
My sqript looks now like this now:
Raw:
LOAD
ShipmentNumber as CPShipmentNumber,
Date(PlanStartDate,'YY-MM-DD') as CPPlanStartDate,
Time(PlanStartTime,'hh.mm') as CPPlanStartTime,
Date(PlanEndDate,'YY-MM-DD') as CPPlanEndDate,
Time(PlanEndTime,'hh.mm') as CPPlanEndTime,
Hour(PlanStartTime)&'-'&(Hour(PlanStartTime)+1) as CPPlanStartHour,
NumberOfVehicles as CPNumberOfVehicles,
SQL SELECT *FROM lsp.LOShipmentTasks where (Len('CheckPoint')>1 and NumberOfVehicles>0);
Left Join
LOAD ShipmentNumber as CPShipmentNumber,
CheckInDate as CPCheckInDate,
CheckInTime as CPCheckInTime;
SQL SELECT *FROM LSP.LOShipment;
/*Then we combine the date and time to create timestamps that Qlik can properly add/subtract, and then we use IterNo to get as many rows as there are vehicles: */
Parsed:
Load *,
((CPPlanEnd-CPPlanStart)/CPNumberOfVehicles) as CPTimePerVehicle1,
((CPPlanEnd-CPCheckInStart)/CPNumberOfVehicles) as CPTimePerVehicle2,
IterNo() as CPVehicle#
While IterNo()<=CPNumberOfVehicles;
Load
CPShipmentNumber,
CPPlanStartTime,
CPPlanEndTime,
CPCheckInTime,
TimeStamp(CPPlanStartDate + Time#(CPPlanStartTime, 'hh.mm')) as CPPlanStart,
TimeStamp(CPPlanEndDate + Time#(CPPlanEndTime, 'hh.mm')) as CPPlanEnd,
TimeStamp(CPCheckInDate + Time#(CPCheckInTime, 'hh.mm')) as CPCheckInStart,
1 as CPNumberOfVehicles1,
CPNumberOfVehicles
Resident Raw
Where CPNumberOfVehicles>=1;
/*Then we calculate the equal portions of time for each vehicle, and we use Peek to compare each row with the previous row.
If it's the same shipment number, then we take the previous end time as the start time, and we add time to the start to get the new end time. */
Final:
Load *,
CPTimePerVehicle1*[CPVehicle#] as TimeToEnd,
If(Peek(CPShipmentNumber)=CPShipmentNumber, Time(Peek(CPNewEndTime),'hh.mm'), Time(CPPlanStart, 'hh.mm')) as CPNewStartTime,
If(Peek(CPShipmentNumber)=CPShipmentNumber, Time(Peek(CPPlanStart)+(CPTimePerVehicle1*[CPVehicle#]),'hh.mm'), Time(CPPlanStart+CPTimePerVehicle1, 'hh.mm')) as CPNewEndTime,
If(Peek(CPShipmentNumber)=CPShipmentNumber, Hour(Peek(CPNewEndTime))&'-'&(Hour(Peek(CPNewEndTime))+1), Hour(CPPlanStartTime)&'-'&(Hour(CPPlanStartTime)+1)) as CPNewPlanStartHour,
If(Peek(CPShipmentNumber)=CPShipmentNumber, Time(Peek(CPNewCheckInEndTime),'hh.mm'), Time(CPCheckInStart, 'hh.mm')) as CPNewCheckInTime,
If(Peek(CPShipmentNumber)=CPShipmentNumber, Time(Peek(CPPlanStart)+(CPTimePerVehicle2*[CPVehicle#]),'hh.mm'), Time(CPCheckInStart+CPTimePerVehicle2, 'hh.mm')) as CPNewCheckInEndTime,
If(Peek(CPShipmentNumber)=CPShipmentNumber, Hour(Peek(CPNewCheckInEndTime))&'-'&(Hour(Peek(CPNewCheckInEndTime))+1), Hour(CPCheckInTime)&'-'&(Hour(CPCheckInTime)+1)) as CPNewCheckInStartHour
Resident Parsed
Order By CPShipmentNumber, [CPVehicle#];
Hello again Lauri!
I Think I´ve solved it at last. There was a date/time filed in my database that was not formated correct....
No it works!
Thanks again Lauri for all your help! 😀
// Magnus
Great! Glad it works now. (I don't think you need the field TimeToEnd that I created; it was just for testing.)