5 Replies Latest reply: May 18, 2015 8:03 AM by Christian Wylezol RSS

    Create calculated Table in Script with allocation keys

    Christian Wylezol

      Good Morning everybody,

       

      I have a question regarding the creation of a table in the script, from which one field is calucalted.

      A join to the existing table would not be good.

      We have eight different keys and for every key we Need a table or one Table with all keys.

       

      I.e.

      I have a Turnover-Table sorted by Code H (i.e. 14001,13001 and so on) --> see screen.

      I have the EUR-Amount and a calculated %(T1-Key)

      The %(T1-Key) ist calculated with in the Script

       

      Set Var_T1_Key =(sum(if (Add5 = 'Gross Sales', Valuehome,0))/aggr(NODISTINCT sum(Valuehome),CODE_C_Code));

       

       

      Now the Challenge is:

      I need to create a new table with the Information of the calculated keys:

      Field 1: CODE_H_Code

      Field 2: T1-Key

      Field 3, Year-Period-Field (will be included later)

       

        • Re: Create calculated Table in Script with allocation keys
          Friedrich Hofmann

          Hi Christian,

           

          you will have to build a FOR ... TO loop for such a thing:

          - First you do a FIRST 1 LOAD with those three fields you want in your new table

          - Then you do such a loop with the NoOfRows() function (rows in your original table) as the max (so it will read

             >> FOR i = 0 TO (NoOfRows('[table]')-1) <<

          - In every iteration, you can use the CONCATENATE option to append the current Code_H and T1_Key to this table

          => After the loop has completed, you should have the same nr. of rows as in your original table (plus 1, which is the empty row you have extracted for a dummy, you can throw this out now)

           

          HTH

          • Re: Create calculated Table in Script with allocation keys
            Peter Cammaert

            You mean, you want to write some script code that does the same thing as the P&L Pivot Table, right?

              • Re: Create calculated Table in Script with allocation keys
                Christian Wylezol

                Hi Peter,

                 

                the T1-Key is already calculated in the Script.

                [The PivotTable show the turnover in one column and the other column show the T1-Key.]

                I now want to write the result of the calculation as value into a new table with the 3 columns mentioned above (CODE_H_Code, T1-Key and Year-Period).

                 

                The Advantage of that would be a table with all keys for all month that I can easily use whenever I want without duplicating that Formular in every Report (Pivot).

                 

                I will attach for you the file I use. The Sheet Umsatz PL10xxx (Filtered on DEDETDEC) shows the database.

                 

                 

                Maybe this will help to understand.

                 

                Very much thanks in advance.

                  • Re: Create calculated Table in Script with allocation keys
                    Peter Cammaert

                    I don't think the T1_Key value is precalculated in the script. There is a SET statement in tab Keys, but that doesn't do anything except setting a variable to a string value. The actual calculation will be done in the Pivot Table object.

                     

                    I think the best way to approach this chalenge is to copy fields Valuehome, E14 and Docdate of all rows from PL10110_Umsatzbuchungen into a new table in your script. Then create a mapping table that maps E14 to CODE_H_CODE in Fire.xls_Budgetgruppen. Now add CODE_H_CODE to your new internal table. You may have to drop all rows that have no mapping, but that depends on which transactions should be included in your table.

                    Then perform a RESIDENT LOAD of Sum(Valuehome) with a GROUP BY on CODE_H_CODE and your preferred translation of Docdate (MonthYear or something).

                     

                    Next calculate a variable that sums the value of Valuehome in all rows. Call it vTotalSum.

                    Reload your new table and add ValueHome / vTotalSum as T1_Key.

                     

                    Is this something you can work on? If not, just ask. I don't have enough information (or data, or time) to do it myself in your document, but it doesn't look that difficult.

                     

                    Peter