Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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)?
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
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.
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
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
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.
Hi,