Recursive function inside a FOR

[PedroZ]

Greetings,

I recently started working with QlikSense and I'm currently having trouble figuring out how to implement a recursive function I developed inside QlikSense's Script.

For example, I have a table that contains: Product, DaysInStock, InitialPrice and Interest. Each product has its own value of each field and the function that describes the price at a certain time ("price(t)") refers to itself at (t-1), so I wanted for the function to calculate each step until it reached the value for price(DaysInStock).

How do I implement something like this in QlikSense? I am doing everything directly through the script.

Example of the algorithm:

DayCounter = 0;
Price = InitialPrice;
for(DayCounter<=DaysInStock):
         Price = Price * (1+DayCounter*Interest);
        DayCounter = DayCounter+1;

For each line, adding a collumn of price values refering to each product.

Kind regards,

Pedro

[Anil_Babu_Samineni]

May be this?

SET DayCounter = 0;
If Price = InitialPrice Then  // I assume, Price and InitialPrice are fields not the variables
         for i=0 to $(DayCounter) STEP (DayCounter<=DaysInStock)
                  LET Price = Price * (1+$(DayCounter)*Interest);
                  LET DayCounter = $(DayCounter)+1;

         Next i

End If

[jonathandienst · Accepted solution]

A For loop like this can be quite inefficient and will perform poorly for larger data sets. I would try something like this instead:

LOAD DayCounter,
	Price * (1 + DayCounter * Interest) as Price,
	DaysInStock,
	Interest
;
LOAD IterNo() as DayCounter,
	*
While IterNo() <= DaysInStock
;
LOAD InitialPrice as Price,
	DaysInStock,
	Interest
From ...

This will expand the table to produce multiple rows per row from the source table

[PedroZ]

First of all, thank you for your help,

As I have never used an "if...then" in QlikSense, where in the script should I put it?

---

@Anil_Babu_Samineni wrote:

May be this?

SET DayCounter = 0;
If Price = InitialPrice Then  // I assume, Price and InitialPrice are fields not the variables
         for i=0 to $(DayCounter) STEP (DayCounter<=DaysInStock)
                  LET Price = Price * (1+$(DayCounter)*Interest);
                  LET DayCounter = $(DayCounter)+1;

         Next i

End If

---

Because even though my script is running, the data in the table does not apply the if function.

Basically, I had some table sources that I joined in one table with all the parameters I need.

Should I put it before creating the tables or after? Even when trying to create another table after the if statement and calling the Price field, it does not compute the value.