I'm hoping you can help me. I want to identify the invoices that;
(#1) have been booked against each other (debit credit) per relation
#2) have been booked against each other per relation on the same day.
And I would like to do this using Set Analysis .
Below is the preferred outcome:
|Invoice||Date||Relation||Amount||#1 Expected||#2 Expected|
I've managed to make the following (check qvw), but It's still missing a hit in the first column at invoice #3 (-3000) and it's missing a hit at the second column at invoice #7 (7700):
Thanks in advance.
Relation B seems to be repeating several times, is there a field which identify that 8th row is reversing the 7th row except the fact that one is 7700 and other is -7700? What if, we have -2000 10 days later? Would we assume that it is reversing 5th row? Also, what if a particular amount repeats multiple times and reversed one less time... for example.. I have 2000 6 times and -2000 on 5 times...
I don't think this can be done solely on the front end... you would need to create some kind of flag to identify these in the script... are you open to script changes?
I already had that feeling, but it was worth a try. 😉
If SA doesn't work, then a script solution is also OK.
Do you have a suggestion to get me going?
"is there a field which identify that 8th row is reversing the 7th row except the fact that one is 7700 and other is -7700? "I
Yes, after a long search in the the data,... I found a key! 🙂
It's composite key. I've added (as example) two fields in the qvw. The combination of fields makes it possible to identify the invoices that are related to each other. So in this example :
- 23 & d2 represent the offset of 3000 and -3000
- 10 & a2 represent the offset of 7700 and -7700
This should make things a little easier, I think? But what to do next? Now that a key can be made, is it possible to use Set Analysis?