Using Set Analysis to identify offset invoices

[cdss-developer]

Hello experts,

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
and
#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
1	10-10-2019	A	3000	-1	-1
2	11-10-2019	B	4000
3	10-10-2019	A	-3000	-1	-1
4	11-10-2019	C	4000
5	11-10-2019	B	2000
6	11-10-2019	C	5000
7	11-10-2019	B	7700	-1
8	12-10-2019	B	-7700	-1

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.

Greetings,
Eelco

[sunny_talwar]

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...

[cdss-developer]

Spoiler

Hi Sunny,

Thanks for you're swift response. 

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?
=> No unfortunately, I can not find a identifier to link the debit and credit invoices. That's the reason I'm looking at this with a alternative approach. My goal is to create an expression with which I can identify the invoices that have been settled with each other.


What if, we have -2000 10 days later? Would we assume that it is reversing 5th row?
=> I want to start with identifying all invoices of the same relation on the same date that have been settled with the same amount. From there I will try extend the expression, by changing the date to a period if necessary (for example date -5/+5 days). Yeah, I know this is tricky, but it's the only solution I can think of right now.

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'm aware that I'm going to run into this. This is also something I want to tweak in parallel during the analysis as I add more data and gain more insight into the data and the (im)possibilities. 
Maybe during development I can find other useful variables to add to specify my query.

I have encountered such a problem more often, and see this as a challenge develop some kind of algorithm for this. 🙂

Greetings,
Eelco

[sunny_talwar]

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?

[cdss-developer]

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?

[sunny_talwar]

I will play around with it and get back to you on this.

[cdss-developer]

UPDATE:

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

[sunny_talwar · Accepted solution]

Try attached

[cdss-developer]

Hi Sunny,

Thanks, this is  working for me!

Greetings,
Eelco