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.
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)
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.
Only additional thought uis load the data as is but blank out (set to Null) the LINE field if not containing Constauncy then any sums on the tables in the display should work fine and there is no group by temp tabels left joins etc
I have already managed to fulfil a similar requirement using the charts/display, this particular requriement involves generating a csv file after the load is completed, specifically for system integration purposes, a chart therefore is not an option.
Would appreciate any other hits if anyone has come across a similar requirement.