Find Overlap not working with dates

Hi!

I’m trying to perform a simple Find Overlap between two tables comparing a Date field but the Find Overlap module is not working as it should.

Here’s my setup and result:

Table 1:

Table 2:

Setup:

Result:

As you can see the Werstellung rows should only match with 3 rows of the Date field. But the module is returning all the 5 rows and not effectively filtering them.

Is there anything I’m missing?

Thank you!

Hey @Gustavo_Dias,

The way you currently have it set up is like telling Parabola “keep any rows in Table 1 where the value in Werstellung is a value that is also anywhere in the Date column of Table 2”.

So, since 10/12/2021 is a value that is in the Date column of Table 2, it will keep all rows in Table 1 where the value in Wertstellung is 10/12/2021.

You could narrow down by adding another condition to the Find Overlap step - ie, you could say "Keep rows from “Bank - insert row” where all of these match:

-Werstellung to Date
-Beschreibung to Payee

I’d need to know more about what you’re trying to do to offer a specific fix, but I hope that helps explain the way you have the “Find Overlap” module set up. Let me know if you have other questions or can provide more details.

Hi @Brian_Dawson

Thank you so much for the quick response!!
You’re completely right. My logic didn’t make much sense. I was trying to simplify the problem to the most to ask a simpler question and forgot to add the other conditional.

But when I add the other conditional the way you suggested I get some inconsistent results. Maybe I’m making another simple logic mistake I can’t see.

This is my setup for the same tables:

If I understood correctly, I should now get 3 results where Wertstellung / Date AND Beschreibung / Payee match on both tables. But I’m only getting one result.

Hi @Gustavo_Dias,

Happy to help. Your date format looks different now, so that could theoretically cause an issue if you don’t have both of the dates in the same format (it’s a good practice to make sure you have a format dates step before using the dates fields in any other operations).

But I think the issue here may just be that you have it set to “Remove” rows now. So Bank Row 5 is the only row from table 1 where the two conditions aren’t met. Change it back to “Keep” rows and see if that works.

Hi Brian!

The dates are consistent. I had the Format Dates added to keep the dates consistent to YYY/MM/DD on both tables.

But I think the issue here may just be that you have it set to “Remove” rows now. So Bank Row 5 is the only row from table 1 where the two conditions aren’t met. Change it back to “Keep” rows and see if that works.

Ant this is the problem. There are only 2 rows where this condition IS met.

So I’m saying "remove rows from BANK where Wertstellung = Date AND Beschreibung = Payee AND Betrag = Amount.

BANK table:

APP table:

Two rows match this critera. Rows 3 and 4 should be removed from the BANKS table.

However, this is the result I’m getting:

Hmmm, that is odd. I’m not sure why that’s happening. Maybe the Parabola team can offer some insight.

But, I have a functional workaround.

Before doing the Find Overlap step, create a new text column for both tables, where you merge the three fields you’re looking to compare.

Table 1

Table 2

Then do the Find Overlap step just using the new unique fields and you should get your desired result.

Nice suggestion there @Brian_Dawson!

@Gustavo_Dias feel free to email a link to the flow to help@parabola.io and we’re happy to take a look. It would be helpful if you can mention some specific rows that you’re seeing in the “Find overlap” results that you do not think should be there, and also rows that should be included however you’re not currently seeing them.

One theory is that leading/ trailing spaces could be causing the issue, which can be removed with the Clean Data step. Another theory is that you need to use the “Format Numbers” step before making the comparison, similar to Format Dates.

Will let you know what we can find after digging into the flow!

1 Like