11 Replies Latest reply on Oct 1, 2012 10:09 AM by Kirk_1

    Proper scripting

    Kirk_1

      Title

      Proper scripting

      Post

           The script below is part of a set field.

           This is what i need it to do... if $fak = 50 then add Fontier_Mars::AE_22 to Fontier_Mars::CF_22

           It does not.

           Is it because i have 2 evaluations? How can i script it?

            

           Case (
               $fak = 50 ; $cl50= Fontier_Mars::AE_22 + Fontier_Mars::CF_22 ;
               $fak = 55 ;  $cl55= Fontier_Mars::AE_21 + Fontier_Mars::CF_21  ;
                           $fak = 60 ;  $cl60= Fontier_Mars::AE_20 + Fontier_Mars::CF_20   ;
               $fak = 65 ;  $cl65= Fontier_Mars::AE_19 + Fontier_Mars::CF_19  ;
                           $fak = 70 ;  $cl70= Fontier_Mars::AE_18 + Fontier_Mars::CF_18   ;
               $fak = 77.5 ; $cl77= Fontier_Mars::AE_17 + Fontier_Mars::CF_17  ;
                           $fak = 85 ;  $cl85= Fontier_Mars::AE_16 + Fontier_Mars::CF_16  ;
               $fak = 92.5 ; $cl92= Fontier_Mars::AE_15 + Fontier_Mars::CF_15  ;                 
               $fak = 100 ;  $cl100= Fontier_Mars::AE_14 + Fontier_Mars::CF_14  ;
               $fak = 110 ; $cl110= Fontier_Mars::AE_13 + Fontier_Mars::CF_13  ;
                           $fak = 125 ; $cl125= Fontier_Mars::AE_12 + Fontier_Mars::CF_12  ;
               $fak = 150 ; $cl150= Fontier_Mars::AE_11 + Fontier_Mars::CF_11  ;
                           $fak = 175 ;  $cl175= Fontier_Mars::AE_10 + Fontier_Mars::CF_10  ;
               $fak = 200 ;  $cl200= Fontier_Mars::AE_09 + Fontier_Mars::CF_09  ;
                              $fak = 250 ; $cl250= Fontier_Mars::AE_08 + Fontier_Mars::CF_07  ;
                           $fak = 300 ; $cl300= Fontier_Mars::AE_07 + Fontier_Mars::CF_06 ;
                              $fak = 400 ; $cl400= Fontier_Mars::AE_06 + Fontier_Mars::CF_04  ;                 
               $fak= 500 ; $cl500= Fontier_Mars::AE_05+ Fontier_Mars::CF_03  ;
           )

        • 1. Re: Proper scripting
          philmodjunk

               It's because you have $cl50= everywhere. This turns your sum into a logical comparison with the value of this variable. It then returns 1 for True or 0 for False.

               If you want to assign this sum to $cl50, use set variable and leave $cl50= out of the expression:

               Set Variable [ $cl50 ; value: Case ( $fak = 50 ; Fontier_Mars::AE_22 + Fontier_Mars::CF_22

          • 2. Re: Proper scripting
            Kirk_1

                 I got the other to by changing it to:

                 Let ( w = $weight ;

                 Case ( w < 500 ; "L5C" ;
                            w < 1000 ;500 ;
                            w < 2000 ; 1000 ;
                            w < 5000 ; 2000  ;
                            w < 10000 ;5000 ;
                            w < 20000 ; 10000
                 )

                  

                 Next: I changed the second one to:

                 Set Value: $adjustedClass MultiplierOZip

                 Case (
                     $fak = 50 ; Fontier_Mars::AE_22 + Fontier_Mars::CF_22 ;
                     $fak = 55 ;   Fontier_Mars::AE_21 + Fontier_Mars::CF_21  ;
                     $fak = 60 ;  Fontier_Mars::AE_20 + Fontier_Mars::CF_20   ;
                     $fak = 65 ;  Fontier_Mars::AE_19 + Fontier_Mars::CF_19  ;
                     $fak = 70 ;   Fontier_Mars::AE_18 + Fontier_Mars::CF_18   ;
                     $fak = 77.5 ;  Fontier_Mars::AE_17 + Fontier_Mars::CF_17  ;
                     $fak = 85 ;   Fontier_Mars::AE_16 + Fontier_Mars::CF_16  ;
                     $fak = 92.5 ; Fontier_Mars::AE_15 + Fontier_Mars::CF_15  ;                 
                     $fak = 100 ;   Fontier_Mars::AE_14 + Fontier_Mars::CF_14  ;
                     $fak = 110 ;  Fontier_Mars::AE_13 + Fontier_Mars::CF_13  ;
                     $fak = 125 ;  Fontier_Mars::AE_12 + Fontier_Mars::CF_12  ;
                     $fak = 150 ; Fontier_Mars::AE_11 + Fontier_Mars::CF_11  ;
                     $fak = 175 ;  Fontier_Mars::AE_10 + Fontier_Mars::CF_10  ;
                     $fak = 200 ;  Fontier_Mars::AE_09 + Fontier_Mars::CF_09  ;
                     $fak = 250 ;  Fontier_Mars::AE_08 + Fontier_Mars::CF_07  ;
                     $fak = 300 ;  Fontier_Mars::AE_07 + Fontier_Mars::CF_06 ;
                     $fak = 400 ;  Fontier_Mars::AE_06 + Fontier_Mars::CF_04  ;                 
                     $fak= 500 ;  Fontier_Mars::AE_05+ Fontier_Mars::CF_03  
                 )

                 This does not produce a result. How else can i script this so it finds the correct value for $fak and add the two fields together?

                  

            • 3. Re: Proper scripting
              LaRetta_1

                   Hi Kirk,

                   Whenever you have multiple 'similar' names (at least 22 seen AE and 22 CF and as many summary fields)  it indicates that those 22 AE fields should be 22 records with Type= "AE" ...same with CF.  Your setup is flat file like Excel instead of utilizing the power of relational structure.  If records instead of fields, this calculation would be one line and everything you do in this file will get 100-times easier.

                   I realize we can't always change things but if at all possible, it should be changed and the sooner the better.  You will not regret making the change. We can provide an example if you wish.  :-)

              • 4. Re: Proper scripting
                Kirk_1

                     Im open.

                     The file or data base that this information is pulling from is a table containing 6 million records. The 22 records are a found set that need to be kept together as a "unit".

                     How do i do it?

                     In the mean time ill make an additional set varable, and add the two set varabels together... the one representing AE  and one representing CF...

                • 5. Re: Proper scripting
                  philmodjunk

                       In the example above, you don't have 22 records, you have 22 fields.

                  • 6. Re: Proper scripting
                    Kirk_1

                         You are correct.

                         The found set is one record with 22 fields in it.

                         The data base or file has 6,335,510 recoreds that contain between 4 and 25 fields per record.

                          

                    • 7. Re: Proper scripting
                      Kirk_1

                           I don't understand why this does not work?

                            

                           If....  this statetment is true... which it is..

                           Set Value: $MarsMin

                           Case (
                                           Fontier_Mars::BR_02  > Fontier_Mars::BR_03 ; Fontier_Mars::BR_02 )

                           Else

                           Set Value: $MarsMin

                           Case (
                                           Fontier_Mars::BR_03  > Fontier_Mars::BR_02 ; Fontier_Mars::BR_03 )

                      • 8. Re: Proper scripting
                        LaRetta_1

                             Hi Kirk,

                             What is the purpose of this table?  All of these fields relate to this table in what way?  For example, if the purpose of the table is to track actions on a product, the table would be product and the multiple 'like' fields might represent months of the activity.  This is only example of what I need to know ...how do these 22 fields relate to this table?  What is the primary unique key for this table?

                             If you provide me a brief explanation, I can put together of a simpler structure for you as I suggested.  You are also welcome to message me and share your file directly ... this would make it easier for me to provide you with a meaningful example.  Regardless whether i use your file or not, I will create an example once I know the purpose and primary entity that each record represents.  :-)

                              

                              

                        • 9. Re: Proper scripting
                          JimMac

                               Kirk, in your 2nd posted  Case statement you don't have a default value. 

                               So if...

                               $fak = 50.1

                               Then...

                                $adjustedClass MultiplierOZip  = ""  or empty

                               As a test you might set the Case default value to be $fak just to make sure it is one of the "exact match" tests you have in the Case.

                               My point is $fak must be one of the Tests within the statement or you will get "" or empty.

                               Jim...

                          • 10. Re: Proper scripting
                            JimMac

                                 In your most recent post above, try this ...

                                  

                                 Set Value: $MarsMin
                                  
                                 Case ( Fontier_Mars::BR_02  >= Fontier_Mars::BR_03 ; Fontier_Mars::BR_02 ;  Fontier_Mars::BR_03)
                                  
                                 The Bold is the default and I added >= to assure all possibilites are covered, not sure if that works for you.
                                  
                                 But ... the $MarsMin will...
                                  
                                 Be set to BR_02 when it is greater than or  equal to BR_03 and when it is not true then it will be set to default of BR_03.
                                  
                                 Jim...
                            • 11. Re: Proper scripting
                              Kirk_1

                                   La Retta

                                   I'm programming a rate engine for LTL transportation.

                                   The rates are based on zip code to zip code, class and weight.

                                   The zip code to zip code determines the distance or the rate basis number.

                                   The class and weight are additional factors in determining the rate. 

                                   The table is the rate base. If i get a found set for a rate basis number, it provides at least 22 fields. I need all of those field, plus the 22-26 fields from another look up (class) to perform the calculations to get the final rate.

                                   The found set provides me all possible scenarios, weights from 1 lb to 10,000. I need to figure out which field i need based on shipment profile. So I do calculations on the found set like excel...

                                   Just like any good government intuition they made exceptions for everything. So i need to make additional look ups for the exceptions.

                                   I have 3 separate scripts. This one script is just for the rate base table. In this one alone i have 14 if statements and have to do a look up and different calculation for each if.

                                   I did not see another way to set up the table to do the look ups. Plus I've spent hour upon hour to program this way, and I'm now done.

                                   I'm sure there will be a next generation version. I'm open... What is your email, ill send the file i received from the owners of the data and the instructions.