Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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
4 Replies
Not applicable

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)

Not applicable

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

Not applicable

Aggregation of debtor line items in the script

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

Not applicable

Aggregation of debtor line items in the script

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