5 Replies Latest reply: Mar 17, 2013 11:33 AM by Nandor Santa RSS

    Import subtotals

      Dear all,

       

      I have the following situation:

       

      1. I have a table like this:

       

      Budget_Acount    Client name     Value

       

      3028                        XXX             110

      3028                       YYY             50

      321                          TTT             90

      321                         KKK            100

       

       

      I need to import this table with subtotals based on the first column. My table when previewd in table viewer, should look like this:

       

      Budget_account       Value

      3028                        160

      321                          190

       

      How can I do this in the script?

       

      Thank you,

        • Re: Import subtotals
          Nagaian Krishnamoorthy

          If the name of your table is DataTable, use the following script to get the result you want:

           

          ResultTable:

          LOAD Budget_Account, Sum(Value) as Accout_Total

          Resident DataTable

          Group By Budget_Account;

            • Re: Import subtotals

              Please reat the code below, it gives an error and I cant figure out what is the problem:

               

               

               

              Cheltuieli_realizate_desfasurat:

              LOAD

                  cd as Cont_furnizor,

                  data as Data_cheltuieli,

                  Year (data) as An,

                  Month(data) as Luna,

                  cont as Cont_buget_cheltuieli,

                  ndp as Nr_factura_furnizor,

                  // explicatie,

                  suma_d as Valoare_realizata_cheltuieli_desfasurat

              FROM

              [..\..\Bugetare\Tabel 2.3 Buget cheltuieli realizat.xlsx]

              (ooxml, embedded labels, table is [Bugete realizate]) Where ndp<> '' and cd<>'603' and cont<>'6028' and cd<>'6028' and cont<>'603';

               

              Cheltuieli:

               

              LOAD

              An,

              Luna,

              Cont_buget_cheltuieli,

              Sum(Valoare_realizata_cheltuieli_desfasurat) as Valoare_realizata_cheltuieli

              Resident Cheltuieli_realizate_desfasurat

              Group By Cont_buget_cheltuieli;

               

               

               

               

               

              THIS IS THE ERROR I GET:

               

              Invalid expression

              Cheltuieli:

               

              LOAD

              An,

              Luna,

              Cont_buget_cheltuieli,

              Sum(Valoare_realizata_cheltuieli_desfasurat) as Valoare_realizata_cheltuieli

              Resident Cheltuieli_realizate_desfasurat

              Group By Cont_buget_cheltuieli

                • Re: Import subtotals
                  Nagaian Krishnamoorthy

                  Try the following script:

                   

                  Cheltuieli_realizate_desfasurat:

                  LOAD *,

                      Year (data) as An,

                      Month(data) as Luna;

                  LOAD

                      cd as Cont_furnizor,

                      data as Data_cheltuieli,

                      cont as Cont_buget_cheltuieli,

                      ndp as Nr_factura_furnizor,

                      // explicatie,

                      suma_d as Valoare_realizata_cheltuieli_desfasurat

                  FROM

                  [..\..\Bugetare\Tabel 2.3 Buget cheltuieli realizat.xlsx]

                  (ooxml, embedded labels, table is [Bugete realizate]);

                   

                  Cheltuieli:

                  LOAD

                  An,

                  Luna,

                  Cont_buget_cheltuieli,

                  Sum(Valoare_realizata_cheltuieli_desfasurat) as Valoare_realizata_cheltuieli

                  Resident Cheltuieli_realizate_desfasurat

                  Where ndp<> '' and cd<>'603' and cont<>'6028' and cd<>'6028' and cont<>'603'

                  Group By Cont_buget_cheltuieli;