Aggregation of debtor line items in the script

[#1]

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	COMPANY	DEBTOR	DOC NO	FY	INVOICE DATE	LINE	AMOUNT
2011/4007/4000006816	4007	24109	4000006816	2011	40756	*01.08.2011-31.08.2011-Discount	-500
2011/4007/4000006816	4007	24109	4000006816	2011	40756	*01.08.2011-31.08.2011-Discount	-50
2011/4007/4000006815	4007	24109	4000006815	2011	40756	*01.08.2011-31.08.2011-Constuancy Fee	4000
RESULT AFTER AGGREGATION IN SCRIPT
KEY_FY_CO_DOC	COMPANY	DEBTOR	DOC NO	FY	INVOICE DATE	LINE	AMOUNT
2011/4007/4000006815	4007	24109	4000006815	2011	40756	*01.08.2011-31.08.2011-Constuancy Fee	3450

[#2]

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)

[#3]

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

[#4]

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

[#5]

Hi David,

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.

Thanks all.

Patrick