topic Scripting, Created Fields, and Nested Statements in QlikView

Scripting, Created Fields, and Nested Statements

Mon, 18 Feb 2013 19:46:52 GMT

Hi all,

I have a generic question for you that can be filed under "There has to be a better way."

So I am creating fields after loading data from a text file. A couple of the fields that I'm bringing in are List Price and Sale price. In the script I then calculate Savings $, Savings $, then other measurements to ensure List Price isn't less than Sale Price as that cannot happen.

Instead of doing the code similar to the following, is there a way to save these new fields to use them in the nested loops?

Psuedo code:

If (List Price = < 0,

item price * amt shipped *1 //reverse credit,

if (List Price < Sale Price,

List Price = Sale Price,

If (isnull(List Price)

Unit Price * amount shipped,

List Price as [Revised List Price]

then it gets messier since I need to keep the formula going since the first one essentially found the "correct" list price. Then if the List price is negative, savings is negative, so on and so forth

If (List Price = < 0,

item price * amt shipped *1 //reverse credit,

if (List Price < Sale Price,

List Price = Sale Price,

If (isnull(List Price)

Unit Price * amount shipped,

List Price

Sale Price,

Savings % = 0,

Sale Price as [Savings %]

etc etc etc. I know the above is messy but I cannot share exact code. I hope there is a way to "save" these created fields elsewhere to use in script but I don't think there is a way. I also need to show a table with the raw data in the dashboard, so I can't just do these with expressions when charting (for example).

Thanks!

Re: Scripting, Created Fields, and Nested Statements

Gysbert_Wassenaar — Tue, 19 Feb 2013 11:48:00 GMT

You could store the table into a qvd in between steps or use a resident load. First calculate Revised List Price, then store the table and load it (or load resident) and calculate Savings % on the second run through the data.

Re: Scripting, Created Fields, and Nested Statements

hic — Tue, 19 Feb 2013 12:43:44 GMT

Even better would be to use several preceding Loads:

Load *, // Thrrd Load

if( <some condition>, ... ) as [Gross Margin %]

;

Load *, // Second Load

if( [Revised List Price] < [Sale Price], ... ) as [Savings %]

;

Load // First Load

... as [Revised List Price]

from ...

This way, you can pipe the result of the first Load into a second, and then further into a third. Cleaner and faster than other solutions.

HIC

Re: Scripting, Created Fields, and Nested Statements

Tue, 19 Feb 2013 13:16:37 GMT

I did try this and it worked well but exploring other options. Thanks!

Re: Scripting, Created Fields, and Nested Statements

Tue, 19 Feb 2013 13:17:38 GMT

Henric,

Can you explain a bit further as I am not familiar with preceding loads? Why would you load the 3rd load, followed by 2nd, and then first? Trying to wrap my mind around this is at seems out of order.

Thanks! This is very interesting.

Re: Scripting, Created Fields, and Nested Statements

hic — Tue, 19 Feb 2013 14:37:33 GMT

I agree that it might be confusing. But look at it as nested functions: If you write

Len(Trim(Substring(...))))

then the Substring is evaluated first, since it is closest to the data. The result of the Substring() is sent on to Trim() which in turn sends its result to Len().

Same thing with the Loads. The bottom Load is closest to the data and is evaluated first. It sends its result to the next load etc.

Another way to look at it is that "Load3" loads from "Load2" which in turn loads from "Load1".

HIC