4 Replies Latest reply: Aug 26, 2011 10:40 AM by dpm10785 RSS

    Aggregation of debtor line items in the script

      Dear all,

       

      I am currently looking for a solution to aggregate the following records into one record, on the way the discount line items require elimination, I have attached the below tables as excel file just in case its not readable

       

      this has to be acheived in the script as the output is required to be imported by another system, i suppose the answer utilise "group by" script feature which allows aggregation and a clever set of keys to enable the "Consultancy Fee" information to be retained, only the amount requires aggregation/summing, all other information is redundant as seen in the result table. The logic for aggregation is the following:

       

      for every line item which the same debtor, FY, Invoicedate and Date Range ie. (01.08.2011-31.08.2011) sum the amount, and represent the result in the consultancy fee line item last step is to eliminate the Discount line items.

       

      Thanks for your input on this.

       

      DPM

       

       

      ORIGINAL TABLE

       

      KEY_FY_CO_DOC

      COMPANYDEBTORDOC NOFYINVOICE DATELINEAMOUNT
      2011/4007/40000068164007241094000006816201140756*01.08.2011-31.08.2011-Discount-500
      2011/4007/40000068164007241094000006816201140756*01.08.2011-31.08.2011-Discount-50
      2011/4007/40000068154007241094000006815201140756*01.08.2011-31.08.2011-Constuancy Fee4000

       

       

      RESULT AFTER AGGREGATION IN SCRIPT

      KEY_FY_CO_DOCCOMPANYDEBTORDOC NOFYINVOICE DATELINEAMOUNT
      2011/4007/40000068154007241094000006815201140756*01.08.2011-31.08.2011-Constuancy Fee3450
        • Aggregation of debtor line items in the script

          when loading the reciords do an If on the Line  and change it to Constuancy Fee you will then have a file that looks like your example but all Line fields have constunacy in you can then either do a resident load of this table into a summary version grouping by all required fields or build a chart table in Qlikview that displays all fields and sum(Value)

           

           

            • Aggregation of debtor line items in the script

              Hi David,

               

              thanks for the suggestion above, changing the discount lines to reflect the consultancy fee is certainly a good start. Please allow me  to elaborate the issues I am having with group by.

               

              The data in the attached excel is really a simplified version of the table, in realtiy it does contain a lot of other information and group by requries to create a table with only specific fields that define the basis for aggregation, all other information will be left out at this point.

               

              I am wondering if there is another possibility to get the desired result without using group by, temp tables and left joins, which is the route i see as feasible at this moment in time. The current script is already performing a number of load states and is quite complex, i want to keep the script as slim as possible.

               

              Kind Regards,

               

              Patrick