Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MagnusRydberg1
Contributor II
Contributor II

How do I create a new file in qlik sense sqript based on data from an existing file.

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.
Labels (1)
1 Solution

Accepted Solutions
Lauri
Specialist
Specialist

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;

View solution in original post

6 Replies
Lauri
Specialist
Specialist

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
MagnusRydberg1
Contributor II
Contributor II
Author

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

Lauri
Specialist
Specialist

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;

MagnusRydberg1
Contributor II
Contributor II
Author

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#];

MagnusRydberg1
Contributor II
Contributor II
Author

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

 

Lauri
Specialist
Specialist

Great! Glad it works now. (I don't think you need the field TimeToEnd that I created; it was just for testing.)