Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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.
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
I did try this and it worked well but exploring other options. Thanks!
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.
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