3 Replies Latest reply: Aug 2, 2011 5:27 PM by Edward Hart RSS

    Load Script - Sum against reference

      Hi,

       

      I'm looking for advice on what I feel should have a really easy solution (but for some reason I cant get it to work).

       

      I have a table similar to the one below: -

       

      Refno
      11
      11
      11
      21
      21
      21
      11
      11
      21
      31
      31
      31
      11
      11

       

       

       

      What I am trying to do is sum 'No' against the 'Ref' within the load script to produce: -

       

      Refnotot
      117
      117
      117
      214
      214
      214
      117
      117
      214
      313
      313
      313
      117
      117

       

       

       

      I have tried if(PREVIOUS(ref)=ref, PEEK([no]),0) + [no] as test, however this does not give the result I am after.

       

      Can anyone advise on how I can achieve this within the load?

       

       

      Many thanks

        • Load Script - Sum against reference

          Think I found the solution using count and grouping.

           

          Main:

          LOAD Ref,

               [no],

               tot

          FROM

          data.xlsx

          (ooxml, embedded labels, table is Sheet1);

           

           

          Main2:

          LOAD Ref as ref2,

               [no],

               count([no]) as cnt,

               tot

           

           

          RESIDENT Main

          GROUP BY Ref , [no], tot;

           

          DROP TABLE Main;

          • Re: Load Script - Sum against reference
            John Witherspoon

            You still want all of the rows, but with the total per Ref on each row?

             

            LEFT JOIN (MyTable)
            LOAD
            Ref
            ,count(no) as tot
            RESIDENT MyTable
            ;

             

            That said, I generally recommend against doing totals in the script, as they won't be sensitive to selections.  Of course, if you WANT your totals to not be sensitive to selections, the script is a good place to do that.  But otherwise, this would normally be handled in a chart.  Something like this:

             

            dimension 1 = Ref
            dimension 2 = no
            expression  = count(total <Ref> no)

              • Load Script - Sum against reference

                Many thanks for that answer. 

                 

                The reason I am trying to do this in the load script is because I need to include the data within a pivot table against the reference; the pivot being on date and containing other information.  Using a count/sum calculation within the dimension just seemed to break the table, I think doing it this way will give a cleaner result (or at least one that will work).