topic Re: Using Set Analysis to identify offset invoices in QlikView

Using Set Analysis to identify offset invoices

cdss-developer — Sat, 16 Nov 2024 19:10:51 GMT

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

Re: Using Set Analysis to identify offset invoices

sunny_talwar — Wed, 05 Feb 2020 13:15:16 GMT

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

Re: Using Set Analysis to identify offset invoices

cdss-developer — Wed, 05 Feb 2020 14:45:24 GMT

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

Re: Using Set Analysis to identify offset invoices

sunny_talwar — Wed, 05 Feb 2020 15:04:28 GMT

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?

Re: Using Set Analysis to identify offset invoices

cdss-developer — Wed, 05 Feb 2020 15:15:55 GMT

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?

Re: Using Set Analysis to identify offset invoices

sunny_talwar — Thu, 06 Feb 2020 11:45:11 GMT

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

Re: Using Set Analysis to identify offset invoices

cdss-developer — Thu, 06 Feb 2020 12:19:49 GMT

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?

Re: Using Set Analysis to identify offset invoices

sunny_talwar — Thu, 06 Feb 2020 13:46:43 GMT

Try attached

Re: Using Set Analysis to identify offset invoices

cdss-developer — Fri, 07 Feb 2020 08:38:31 GMT

Hi Sunny,

Thanks, this is working for me!

Greetings,
Eelco