Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Analytics
- :
- Recursive function inside a FOR

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

PedroZ

Contributor

2019-04-23
05:20 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,349 Views

1 Solution

Accepted Solutions

jonathandienst

Partner - Champion III

2019-04-24
01:31 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

3 Replies

2019-04-24
12:43 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2019-04-24
01:31 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2019-04-24
10:46 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 variablesfor 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.

2,311 Views

Community Browser