Script for load inline and row duplication with loop

patricesalem — Mon, 04 Oct 2021 08:11:04 GMT

Hello

I'm trying to find a way to duplicate existing rows with an inline load statement. The result will help me calculating late deliveries for each week until the order has been fully shipped.

I've prepared a QVF sample to find the best solution with your kind support :

ie : I would like the first line to be duplicated (thanks to an load * inline script) 5 times.
Each time, the line would be duplicated incrementing the To_be_delivered_on_week field by one.
The result would be for that line :

In the attached QVF, I have created a set of records to be able to test your proposals:

[ORDER_DATA]:
LOAD * INLINE [
Order_Number, Part_Number, To_be_delivered_on_week, Delivered_on_week
123456,2500,30,35
123456,2600,30,34
123457,3500,33,37
123458,4000,37,37
];

Then the load * inline script I'm looking for would be concatenating its result with the above table...but I don't see how to write it.

Any idea/input would be greatly appreciated.
Thanks
Pat
Qlik Sense Server May 2021

Re: Script for load inline and row duplication with loop

patricesalem — Mon, 04 Oct 2021 09:52:20 GMT

Hello

Thanks to another post (in stackoverflow), I have found the solution :

[ORDER_DATA]: Load * Inline [ ID, Order_Number, Order_Line_Number, To_be_delivered_on_week , Delivered_on_week 123456_10,123456,10,30,35 123456_20,123456,20,30,34 123457_35,123457,35,33,37 123458_40,123458,40,37,37 ]; TempTable: Load distinct ID & '^' & Order_Number & '^' & Order_Line_Number & '^' & [To_be_delivered_on_week] & '^' & [Delivered_on_week] as Id_Weeks Resident ORDER_DATA ; // for each record in Id_Dates field for i = 1 to FieldValueCount('Id_Weeks') // get the current iteration value let value = FieldValue('Id_Weeks', $(i)); // extract the Id let currentId = SubField('$(value)', '^', 1); //Extract Order Number let currentOrder = SubField('$(value)', '^', 2); //Extract Order Line Number let currentLine = SubField('$(value)', '^', 3); // extract week to be delivered let currentWeekStart = Num(SubField('$(value)', '^', 4)); // extract week of delivery let currentWeekEnd = Num(SubField('$(value)', '^', 5)); // autogenerate all weeks between the week to deliver and week of delivery // add the current Id value (this will link to the RawData table) ORDER_DATA_FINAL: LOAD '$(currentId)' as Id, '$(currentOrder)' as Order, '$(currentLine)' as Order_Line_Number, '$(currentWeekEnd)' as Week_of_Delivery, $(WeekStart) + IterNo() - 1 AS Due_Week_of_Delivery AUTOGENERATE (1) WHILE $(currentWeekStart) + IterNo() -1 <= $(currentWeekEnd) ; next // we dont need this table anymore Drop Table TempTable; Drop Table ORDER_DATA;

It works exactly as expected. Feel free to let me know if there is a way to make it better (ie: for the duplication of 500K lines +)

Hope my solution will be used by others, stuck as I've been for a few days (before posting...)
Pat

topic Script for load inline and row duplication with loop in App Development

Script for load inline and row duplication with loop

Re: Script for load inline and row duplication with loop