Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
New Contributor III

Using Set Analysis to identify offset invoices

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:

InvoiceDateRelationAmount#1 Expected#2 Expected
110-10-2019A3000-1-1
211-10-2019B4000  
310-10-2019A-3000-1-1
411-10-2019C4000  
511-10-2019B2000  
611-10-2019C5000  
711-10-2019B7700-1 
812-10-2019B-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):

test.png

 

Thanks in advance.

Greetings,
Eelco

Labels (2)
1 Solution

Accepted Solutions
Highlighted

Re: Using Set Analysis to identify offset invoices

8 Replies
Highlighted

Re: Using Set Analysis to identify offset invoices

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

Highlighted
New Contributor III

Re: Using Set Analysis to identify offset invoices

 

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


 

 

Highlighted

Re: Using Set Analysis to identify offset invoices

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?

Highlighted
New Contributor III

Re: Using Set Analysis to identify offset invoices

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?

Highlighted

Re: Using Set Analysis to identify offset invoices

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

Highlighted
New Contributor III

Re: Using Set Analysis to identify offset invoices

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?

Highlighted

Re: Using Set Analysis to identify offset invoices

Highlighted
New Contributor III

Re: Using Set Analysis to identify offset invoices

Hi Sunny,

Thanks, this is  working for me!

Greetings,
Eelco