Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

1 Solution

Accepted Solutions
Highlighted

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
Highlighted

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted

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

Highlighted
Contributor
Contributor

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.