Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Netting

I have the following table

Table1:

ProductAmount
A-100
A-200
A-300
A30
A50
A50
A150
A60
A150
A200
B-500
B20
B80
B100
B500

I need the following output from this table

Output Needed:

ProductAmount
A30
A60
B20
B80
B100

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)

8 Replies
Ricardo_Gerhard
Employee
Employee

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)?

Ricardo Gerhard
OEM Solution Architect
LATAM
Anonymous
Not applicable
Author

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

its_anandrjs

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.

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Ricardo_Gerhard
Employee
Employee

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

Ricardo Gerhard
OEM Solution Architect
LATAM
vvira1316
Specialist II
Specialist II

Hi,

Netting.JPG