Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Scripting, Created Fields, and Nested Statements

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!

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

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

View solution in original post

5 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
hic
Former Employee
Former Employee

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

Not applicable
Author

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

Not applicable
Author

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.

hic
Former Employee
Former Employee

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