For loop to create dynamic field names in Qlik Sense

[Ostuni]

I have a table (Table1) with the following columns:
Job number | Start date | End date | Reference date | Remaining job amount | Total job days

Remaining job amount: monetary value that signals how much company still needs to pay for the job
Total job days: total number of days that the job is running; calculcated  by end date - start date + 1

I want to create a loop that adds 14 new columns to the table. The column names should be the next 14 month-ends starting from the Refence date. The loop should calculate and assign what remaining job amount is to be paid in the next 14 months, so the company can see what payments are due for those next 14 months.

I do this by calculating the number of days for the 14 next months, divide this number by the total job days and multiply it by the remaining job amount. 

I wrote the following FOR loop for this:

LET vStartDate = MakeDate(2024, 09, 30);
LET vEndDate = MakeDate(2025, 10, 31);
LET vMaxColumns = 14; 
LET vColumnCount = 0;
SET vColumns = ''; 

FOR vDate = vStartDate TO vEndDate STEP 1
   IF Day(vDate) = Day(MonthEnd(vDate)) THEN

      LET vDateFormatted = Date(vDate, 'DD-MM-YYYY');

      LET vCurrentColumn =
         'max(0, min([Start date], Date(' & vDate & ')) - ' &
         'max([Start date], Date(' & vDate & ') - Day(Date(' & 
         vDate & ')) + 1) + 1) * [Remaining job amount] / 
         [Total job days] ' &
         'AS [Month' & vDateFormatted & ']';

      LET vColumns =
         If(Len(Trim(vColumns)) > 0, vColumns & ', ', '') & 
        vCurrentColumn;

      LET vColumnCount = vColumnCount + 1;

      TRACE Generated column: Month_$(vDateFormatted);

      // Stop loop if maximum # columns has been reached
      IF vColumnCount >= vMaxColumns THEN
         EXIT FOR; 
      ENDIF
   ENDIF
NEXT vDate

NoConcatenate
Table2:
LOAD
[Job number],
[Start date],
[End date],
[Reference date],
[Remaining job amount],
[Total job days],
$(vColumns) 
RESIDENT Table1;

By adding the TRACE statement I can see in the log that Qlik is not exiting the loop properly and is not moving on to the next part of the script when it hits the maximum of 14 columns. So, it is stuck at creating the last month-end column 'Month_31-10-2025'. 

I suspect it has to do with my ending of the loop; but I cannot figure out exactly what it is. 

[Kushal_Chawda]

@Ostuni  It doesn't look right approach to create a fields in the script this way. You could do it on frond end expression instead. If you can share sample data with expected output in excel with dummy data, you will get a better solution if possible

[Ostuni]

Thanks for having a look, Kushal. 

Below I have shared sample data. To be clear, the data in columns A to F I have already available in the table. I want to create column G and further. 

I think Qlik should first create the (dynamic) field names which are essentially the first 14 month-end dates after the Reference date. Then it should calculate the numbers as shown in G2 until AF3. 

[rubenmarin · Accepted solution]

Hi, in case it helps: I think it gets stuck because you are using max() and Min(). Try using RangeMax() and RangeMin().

Max and min uses first parameter as the field/value, and the second as the rank, in case you want to get the 2nd, 3rd... max/min.

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/Aggrega...

RangeMax and RangeMin returns the max/min value of all values in parameters.

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/ChartFunctions/Ra...

[Ostuni]

Hi, this was indeed the solution. I replaced all max() and min() within the loop by RangeMax() and RangeMin().

Thanks a lot! 

[Kushal_Chawda · Accepted solution]

@Ostuni  You might have got the solution but as I mentioned, it is not optimal solution as creates columns for each month and involved complex loops. It should be row based so that you can use it as dimension. Just a suggestion, you can take a call based on your scenario

Data:
LOAD
    "Job number",
    "Start date",
    "End date",
    "Reference date",
    MonthEnd("Reference date",IterNo()) as MonthEnd_Next_14_Months,
    "Remaining job amount",
    "Total job days"
FROM [lib://DataFiles/Example table Qlik.xlsx]
(ooxml, embedded labels, table is Sheet1)
While MonthEnd("Reference date",IterNo())<=monthend(addmonths("Reference date",14)); // here you can set the variable to create values for next 'n' months instead of 14

Final:
Load *,
      rangemax(0, floor(RangeMin("End date",MonthEnd_Next_14_Months))-
      RangeMax("Start date",MonthEnd_Next_14_Months-day(MonthEnd_Next_14_Months)+1)+1)*
      "Remaining job amount"/"Total job days" as Required_Next_14_Month_Amount
Resident Data;

Drop Table Data;

[Ostuni]

Hi Kushal,

Thanks again for your support, your solution is more flexible as it is row-based as you said. I implemented it and it works pretty well so far. 

I have one more question: when implementing this in the data load editor, the expression sum([Remaining job amount]) in the front-end is multiplied by 14. Any idea how to account for this in the script?