topic Re: For loop to create dynamic field names in Qlik Sense in Connectivity & Data Prep

For loop to create dynamic field names in Qlik Sense

Ostuni — Sun, 27 Oct 2024 14:35:00 GMT

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.

Re: For loop to create dynamic field names in Qlik Sense

Kushal_Chawda — Mon, 28 Oct 2024 10:32:55 GMT

@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

Re: For loop to create dynamic field names in Qlik Sense

Ostuni — Fri, 01 Nov 2024 15:48:10 GMT

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.

Re: For loop to create dynamic field names in Qlik Sense

rubenmarin — Sat, 02 Nov 2024 08:13:57 GMT

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/AggregationFunctions/max.htm

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/RangeFunctions/rangemax.htm

Re: For loop to create dynamic field names in Qlik Sense

Ostuni — Sun, 03 Nov 2024 13:54:20 GMT

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

Thanks a lot!

Re: For loop to create dynamic field names in Qlik Sense

Kushal_Chawda — Sun, 03 Nov 2024 14:51:38 GMT

@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;

Re: For loop to create dynamic field names in Qlik Sense

Ostuni — Fri, 13 Dec 2024 16:50:36 GMT

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?