topic Re: Netting in QlikView

Netting

Anonymous — Wed, 25 Oct 2017 12:42:53 GMT

I have the following table

Table1:

Product	Amount
A	-100
A	-200
A	-300
A	30
A	50
A	50
A	150
A	60
A	150
A	200
B	-500
B	20
B	80
B	100
B	500

I need the following output from this table

Output Needed:

Product	Amount
A	30
A	60
B	20
B	80
B	100

Logic: For each unique product (A or B) I want to eliminate all amounts that sum up to 0(Zero). I want to only keep those amount fields in the data which do not sum up to 0. This is called netting in finance

Any idea how to get that either in the script or through pivot? (ill prefer script)

Re: Netting

Ricardo_Gerhard — Wed, 25 Oct 2017 15:56:55 GMT

Could you explain further this rule?

Why do you want to keep two values A or B for Product1 instead of a simple sum(product)?

Re: Netting

Anonymous — Wed, 25 Oct 2017 16:02:10 GMT

A & B are two different product ID's. Also the table has several other columns which I have not included here. I want the final table in the format as shown below with each line item preserved (which does not sum up to 0).

This is basically applying cash which has been collected from customers to the corresponding receivables account. Eventually I need the final list of items where cash is yet to be received

Re: Netting

its_anandrjs — Wed, 25 Oct 2017 16:07:15 GMT

When you sum the values for Product you get result

A 90

B 200

and all negative values get minus by positive values ex:- +500 -500 you get 0 same as other values added then why you need to keep all values for A and B, but you get a correct result.

Re: Netting

Anonymous — Wed, 25 Oct 2017 16:09:06 GMT

I do not want to show the sum, but the individual line items that are left out after netting. I need the output as shown in the output table

Re: Netting

Anonymous — Wed, 25 Oct 2017 16:09:29 GMT

This is basically applying cash which has been collected from customers to the corresponding receivables account. Eventually I need the final list of items where cash is yet to be received

Re: Netting

Ricardo_Gerhard — Wed, 25 Oct 2017 16:16:02 GMT

I think the solution would be AGGR, using this current and other field on the seleciton.

Check out this sample: Aggr ‒ QlikView‌

Other solution is group by on load script in order to recreate a new table with grouped result.

QlikView Aggregate Functions

Re: Netting

vvira1316 — Wed, 25 Oct 2017 16:42:51 GMT

Hi,

Re: Netting

MarcoWedel — Wed, 25 Oct 2017 20:42:57 GMT

maybe helpful:

Qlik Community Tip: Posting Successful Discussion Threads

How to get answers to your post?

Its not about the answer, rather it’s about the question…

regards

Marco