8 Replies Latest reply on Jun 10, 2013 9:30 AM by philmodjunk

# Pricing algorithm using Let function

### Title

Pricing algorithm using Let function

### Post

I've been trying to work out a pricing algorithm using Let functions. I'm having a couple of problems.

1.           I would like to reference the next seller instead of using seller 1, seller 2 etc. How do I achieve that in a calculation?
2.
3.           I would also like to exclude certain sellers from the pricing comparison. I tried that with:

Excluded seller 1 = Seller 1  = "Tolka Unused Books" or Seller 1  = "Book-Fare";

But it doesn't seem to be working at excluding these sellers when I use this calcualtion:
Optimum price = If ( Seller 1 ≠ Excluded seller 1 ; (Price 1 + Shipping 1  - Amazon shipping) * .99 ; (Price 2 + Shipping 2 - Amazon shipping) * .99 )
These sellers are not being excluded.
Here is the whole calcuation: Have you any advice?

Let ( [
Price 1 = Parse ( Inventory::source_bookfinder ; "x3b" ; "\x3c" ; 1 ) ;
Shipping 1 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 1 ) ;
Price 2 = Parse ( Inventory::source_bookfinder ; "x3b" ; "\x3c" ; 2 ) ;
Shipping 2 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 2 ) ;
Seller 1 = Parse ( Inventory::source_bookfinder ; "<td class=\"results-table-center\"><span class=\"results-explanatory-text-Logo\">" ; "</span>" ; 1 ) ;
Excluded seller 1 = Seller 1  = "Tolka Unused Books" or Seller 1  = "Book-Fare";
Amazon shipping = "2.80" ;
Optimum price = If ( Seller 1 ≠ Excluded seller 1 ; (Price 1 + Shipping 1  - Amazon shipping) * .99 ; (Price 2 + Shipping 2 - Amazon shipping) * .99 )
] ;
If (Optimum price  ≥ Inventory::floor; Optimum price ; Inventory::floor )
)

• ###### 1. Re: Pricing algorithm using Let function

Excluded seller 1 = Seller 1  = "Tolka Unused Books" or Seller 1  = "Book-Fare";

is a boolean expression. It will return either True (1) or False (0). Thus,  Seller 1 ≠ Excluded seller 1 will almost certainly always evaluate as True.

Try this expression in place of Excluded seller 1:

Excluded Seller List = List ( "Tolka Unused Books" ; "Book-Fare" );

and replace Seller 1 ≠ Excluded seller 1 with:

IsEmpty ( FilterValues ( Excluded Seller list ; Seller 1 ) )

• ###### 2. Re: Pricing algorithm using Let function

Thanks Phil. I made those changes. My calculation looks like this

Let ( [
Price 1 = Parse ( Inventory::source_bookfinder ; "x3b" ; "\x3c" ; 1 ) ;
Shipping 1 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 1 ) ;
Price 2 = Parse ( Inventory::source_bookfinder ; "x3b" ; "\x3c" ; 2 ) ;
Shipping 2 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 2 ) ;
Seller 1 = Parse ( Inventory::source_bookfinder ; "<td class=\"results-table-center\"><span class=\"results-explanatory-text-Logo\">" ; "</span>" ; 1 ) ;
Excluded Seller List = List ( "Tolka Unused Books" ; "Book-Fare" );
Amazon shipping = "2.80" ;
Optimum price = If ( IsEmpty ( FilterValues ( Excluded Seller list ; Seller 1 ) ) ; (Price 1 + Shipping 1  - Amazon shipping) * .99 ; (Price 2 + Shipping 2 - Amazon shipping) * .99 )
] ;
If (Optimum price  ≥ Inventory::floor; Optimum price ; Inventory::floor )
)
But it is still making a comparison to the excluded sellers' prices.
• ###### 3. Re: Pricing algorithm using Let function

Have you check to be sure that each caclulation variable, (price 1, shipping 1, etc...) is being correctly parsed? Have you checked to be sure that the values in the excluded list exactly match the values being parsed for seller 1 when the value for seller 1 is an excluded seller?

• ###### 4. Re: Pricing algorithm using Let function

I switched your filter around. I also checked over my calculation and found that seller 2 was coming up with the wrong calculation. It was returning the shipping cost for seller 1 instead of seller 2's price.

If ( IsEmpty ( FilterValues ( Seller 1 ; Excluded Seller list ) )

So that has sorted me out to a certain extent. Sometimes I need to exclude seller 2 and seller 3 if they are on my excluded seller list.

I would like to reference the next seller instead of using seller 1, seller 2 etc. How do I achieve that in a calculation? Do I need to set a global variable? If so how?

• ###### 5. Re: Pricing algorithm using Let function

It depends on how your calculation is set up. In your example so far, those calculation variables, seller 2 and seller 3 are not part of the calculation.

You might even need a loop--either with a script or a recursive custom function to loop through the values until you reach the one that you don't want to omit.

Another option might be to extract all these values into related records and then use a relationship or scripted find to either omit or delete the records for values that you don't want.

• ###### 6. Re: Pricing algorithm using Let function

I suppose the second option - using related records is the one that I would be most familiar with. But if it was simpler to make a recursive custom function I wouldn't mind trying that.

• ###### 7. Re: Pricing algorithm using Let function

I've tried making my own loop within my calculation. But I don't think it is working properly.

Let ( [
Price 1 = Parse ( Inventory::source_bookfinder ; "Book price\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 1 ) ;
Shipping 1 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 1 ) ;
Seller 1 = Parse ( Inventory::source_bookfinder ; "<td class=\"results-table-center\"><span class=\"results-explanatory-text-Logo\">" ; "</span>" ; 1 ) ;

Price 2 = Parse ( Inventory::source_bookfinder ; "Book price\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 2 ) ;
Shipping 2 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 2 ) ;
Seller 2 = Parse ( Inventory::source_bookfinder ; "<td class=\"results-table-center\"><span class=\"results-explanatory-text-Logo\">" ; "</span>" ; 2 ) ;

Price 3 = Parse ( Inventory::source_bookfinder ; "Book price\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 3 ) ;
Shipping 3 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 3 ) ;
Seller 3 = Parse ( Inventory::source_bookfinder ; "<td class=\"results-table-center\"><span class=\"results-explanatory-text-Logo\">" ; "</span>" ; 3 ) ;

Price 4 = Parse ( Inventory::source_bookfinder ; "Book price\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 4 ) ;
Shipping 4 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 4 ) ;
Seller 4 = Parse ( Inventory::source_bookfinder ; "<td class=\"results-table-center\"><span class=\"results-explanatory-text-Logo\">" ; "</span>" ; 4 ) ;

Price 5 = Parse ( Inventory::source_bookfinder ; "Book price\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 5 ) ;
Shipping 5 = Parse ( Inventory::source_bookfinder ; "Shipping cost\x3c\x2fth\x3e\x3ctd\x3e\x26pound\x3b" ; "\x3c" ; 5 ) ;
Seller 5 = Parse ( Inventory::source_bookfinder ; "<td class=\"results-table-center\"><span class=\"results-explanatory-text-Logo\">" ; "</span>" ; 5 ) ;

Excluded Seller List = List ( "Tolka Unused Books" ; "Book-Fare" );
Amazon shipping = "2.80" ;

Option 1 =  If (IsEmpty ( FilterValues ( Seller 1 ; Excluded Seller List ) ) ; (Price 1 + Shipping 1  - Amazon shipping) * .99) ;
Option 2 = If ( IsEmpty ( FilterValues ( Seller 2 ; Excluded Seller List ) ) ; (Price 2 + Shipping 2  - Amazon shipping) * .99 ; (Price 3 + Shipping 3 - Amazon shipping) * .99 );
Option 3 = If ( IsEmpty ( FilterValues ( Seller 3 ; Excluded Seller List ) ) ; (Price 3 + Shipping 3  - Amazon shipping) * .99 ; (Price 4 + Shipping 4 - Amazon shipping) * .99 );
Option 4 = If ( IsEmpty ( FilterValues ( Seller 4 ; Excluded Seller List ) ) ; (Price 4 + Shipping 4  - Amazon shipping) * .99 ; (Price 5 + Shipping 5 - Amazon shipping) * .99 );

Optimum price = If (IsEmpty ( FilterValues ( Seller 1 ; Excluded Seller List ) ) ; If ( IsEmpty ( FilterValues ( Seller 2 ; Excluded Seller List ) ) ; (Price 2 + Shipping 2  - Amazon shipping) * .99 ; If ( IsEmpty ( FilterValues ( Seller 3 ; Excluded Seller List ) ) ; (Price 3 + Shipping 3  - Amazon shipping) * .99 ; If ( IsEmpty ( FilterValues ( Seller 4 ; Excluded Seller List ) ) ; (Price 4 + Shipping 4  - Amazon shipping) * .99 ; (Price 5 + Shipping 5 - Amazon shipping) * .99 ) ) ))
] ;
If (Optimum price  ≥ Inventory::floor; Optimum price ; Inventory::floor )
)
The last part is a nested function where the defalut value is option 2, 3, up to option 4. I don't think there will be a fifth unwanted book seller. I have tested this formula and it is really hard to see which part is not working. But I can clearly see that it goes as far as the second price and doesn't go onto the third price if it needs to.
Would
Case (option 1: option 2; option 2; option 3; option 3; option 4)
work better? Its much easier to read. But I don't think it would work either.
• ###### 8. Re: Pricing algorithm using Let function

The "loop" would be either in a script or custom function.