Script for load inline and row duplication with loop

[patricesalem]

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

[patricesalem · Accepted solution]

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