Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
PedroZ
Contributor
Contributor

Recursive function inside a FOR

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

Labels (2)
1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

3 Replies
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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
PedroZ
Contributor
Contributor
Author

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.