2 Replies Latest reply: Apr 3, 2015 10:23 AM by Leo Borwick RSS

    Problem with calculated variables in script

      I am trying to add some calculated variables to a table I am inputting from an external file based on a second table which is not linked to the first, using the FieldValue.  The secondary table has eight rows, each of which should produce new calculated fields in the primary table.  I am doing the calculation in stages to keep the code from getting unwieldy.  This means creating a new table for each stage, reading

       

      I am having two problems:

      a) the first three variables calculate ok, but the rest just produce zeroes,

      b) the script goes continues to load the next two stages, but then gives a "Field not found" error in relation to one of the fields (TC1) defined at a previous stage.

       

      The script is copied below.

       

      Thanks!

       

      NewTariffs:

          LOAD * INLINE [

          Tariffs, Monthly Subscription, Overage Charge per MB, MB included

          "A", 0.40, 0.5, 1

          "B", 0.80, 0.3, 2

          "C", 2.05, 0.3, 5

          "D", 3.40, 0.3, 10

          "E", 4.40, 0.3, 50

          "F", 5.15, 0.3, 100

          "G", 3.50, 0.3, 1024

          "TBA",9999.00, 1, 1025

      ];

       

       

        

      Users:

      LOAD

          MSISDN,

          "Invoice Date",

          Username,

          "Home usage (Kb)",

          MRC,

          "Usage charges - home",

          "Usage charges - roaming",

          "Current package cost",

           FieldValue('Monthly Subscription', 1) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 1)) * FieldValue('Overage Charge per MB', 1) as TC1,

           FieldValue('Monthly Subscription', 2) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 2)) * FieldValue('Overage Charge per MB', 2) as TC2,

           FieldValue('Monthly Subscription', 3) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 3)) * FieldValue('Overage Charge per MB', 3) as TC3,

           FieldValue('Monthly Subscription', 4) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 4)) * FieldValue('Overage Charge per MB', 4) as TC4,

           FieldValue('Monthly Subscription', 5) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 5)) * FieldValue('Overage Charge per MB', 5) as TC5,

           FieldValue('Monthly Subscription', 6) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 6)) * FieldValue('Overage Charge per MB', 6) as TC6,

           FieldValue('Monthly Subscription', 7) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 7)) * FieldValue('Overage Charge per MB', 7) as TC7,

           FieldValue('Monthly Subscription', 8) + Rangemax(0, [Home usage (Kb)] /1024 - FieldValue('MB included', 8)) * FieldValue('Overage Charge per MB', 8) as TC8

      FROM [lib://Data for Checker/Usage extract.xlsx]

      (ooxml, embedded labels, table is CH29_20150319_115647);

       

      Sums1:

      LOAD MSISDN,

           sum(TC1) as TC1a,

           sum(TC2) as TC2a,

           sum(TC3) as TC3a,

           sum(TC4) as TC4a,

           sum(TC5) as TC5a,

           sum(TC6) as TC6a,

           sum(TC7) as TC7a,

           sum(TC8) as TC8a

           resident Users group by MSISDN;

       

       

      Sums2:    

      Load  MSISDN,

           RangeMin(TC1a, TC2a, TC3a ) as Cheapest

           Resident Sums1;

       

       

      Sums3:

      LOAD MSISDN, TC1a, TC2a, TC3a,

           pick( match(Cheapest, TC1a, TC2a, TC3a) , FieldValue('Tariffs', 1), FieldValue('Tariffs', 2), FieldValue('Tariffs', 3)) as Best

           Resident Sums2;