Hello,
I'm working on riddle and it's slowly bringing me to my knees.
I have the following dataset:
Amount | Group | Group items count | Type | Comment |
-14689 | A | 2 | N | Long position |
0 | A | 2 | N | Long position |
-50 | B | 3 | N | Long position |
-7 | B | 3 | N | Long position |
-6 | B | 3 | N | Long position |
-59,384.00 | C | 3 | N | Amend the position |
686.00 | C | 3 | N | Amend the position |
3,082.00 | C | 3 | N | Amend the position |
-3768 | C | Y | Amend the position | |
-3000 | D | 2 | N | Amend the position |
200 | D | 2 | N | Amend the position |
-200 | D | Y | Amend the position |
What my workflow should do in this case is:
I hope that I described my "difficulty" and someone may propose a solution.
Thank you in advance.
Solved! Go to Solution.
Hi @michalk36
Please check this, it is not a full solution but I ended up doing most of it and I think it's in line with what you're after. I'm concerned there are a few cases which you might not be caught in your logic and there appears to be no difference between your last two bullets, so the treatment ends up being the same for these cases. Again please check this is what you're after.
I have added numerous ERROR outputs where I think the logic is missing, so should you have any more test data to flow through, you may see these crop up.
Good luck with your further development.
Hi @Jon-B
thanks for sharing your idea! I'll check it out and get back with comments.
I talked with my colleague and he came up with idea to use cross tab with concatenation of each row in group and then feeding batch macro where calculation and decision will be executed.
Currently the solution is being executed with following macro:
I'll try couple approaches.
Thank you for showing the way!
I managed to achieve the solution by taking the macro bit by bit, first I filtered all the values and then applied the formulas/calculations accordingly, once done I summed all the thing and in couple places transposed the columns.
@Jon-B thank you for sharing your idea - it gave me a nice overview how to solve the problem!