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.

xmascarenhas123
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
dcj
Not applicable

Re: Netting

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
xmascarenhas123
Not applicable

Re: Netting

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

Re: Netting

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.

xmascarenhas123
Not applicable

Re: Netting

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

xmascarenhas123
Not applicable

Re: Netting

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

dcj
Not applicable

Re: Netting

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

Re: Netting

Hi,

Netting.JPG