Re: Looking for duplicate entries

Phil_Waite · 01-07-2020 10:54 AM

Hi,

I am looking for a way to compatre values to identify possible duplicates.

I have an expression that just doesn’t seem to work when it should.

Name column in one table has

“Broadstraik Inn”

The column Run Report has

“Broa”

This expression doesn’t seem to give a positive.

CONTAINS([Name],Full List Table[Run Report])

I can get IN to work but not CONTAINS.

Screen shot below with Broa in the list.

Thanks

Phil

LeventK

@Phil_Waite
Try with just the opposite:

CONTAINS(Full List Table[Run Report],[Name])

Phil_Waite

Hi,

I get the same result. Not sure what I’m missing here.

thanks

Phil

LeventK

@Phil_Waite
Are these columns in the same table??

Phil_Waite

Hi

No, this is a slice of a table with the name “Data Import” and the Column “Name”. The other table is “Full List Table” with column “Run Report”.

Thank you

Phil

They are in different google sheets too.

Bahbus

Are all these names the ones in [Run Report]?

Phil_Waite

Hi Dave,

Yes. Broa should be picked up as pasrt of “Broadsraik Inn”

LeventK

@Phil_Waite
As long as both gSheet are imported to the app, it’s not a problem, no worries on that. Try with below assumıng that you are trying this expression from the Data Import table:

CONTAINS(Full List Table[Run Report],CONCATENATE([Name]))

Steve

No, you’re not: “Duplicates” means repeat values, and implies the values are identical. There appears to be nothing “duplicate” or identical about what you’re looking for.

[Name] appears to be a List or EnumList. This is significant. Where does this list come from?

What is in the Run Report column of the Full List Table? What is its column type? These are significant.

Both [Name] and Full List Table[Run Report] are lists. You cannot do partial text matches of items in one list against another list.

Bahbus

@Steve I think [Name] is not a list, but the problem is basically the same. I believe his current setup is a list of things that could potentially be a part of [Name]. He’s trying to do the opposite of what the function can do. He’s not asking if THIS is part of any of THOSE. He’s trying to ask if any of THESE are part of THAT. And I cannot fathom a way to pull that off, without a lot of hardcoding, and what would probably be a very very slow expression to run.

But I also don’t think that this is what he is actually intending to try to accomplish.

Phil_Waite

Hi,

Sorry.This part of my post was misleading.

“I am looking for a way to compare values to identify possible duplicates.”

I’m looking for possibe duplicate outlets in my sales app that occur because of a slight spelling difference in the name.

“Forester’s Arms” Foresters" “Foresters Arms”

To check to see if a new outlet that is being added to the system is already there I am creating a slice that looks for outlets in the sytem already with the same postcode and then whether the first 4 letters of any of the outlets that share that postcode are CONTAINED in the new outlet name.

This wouldn’t be perfect but would identify the majority of the duplictates.

Rows (of the table Data Import - new outlets) to be included in the slice to have:

The same postcode as any of the outlets in the main table Full List Table[Post Code]

AND contain the the 4 letters/text in the column [Run Report] in the [Name] of the outlet.

I could separate the first 4 letters of the [Name] in the new outlet list as I have done for the existing outlets (in column [Run Report] and look for an exact match but thought CONTAINS might provide more coverage in terms of mispellings.

This works below

AND(IN([Post Code],Full List Table[Post Code]),IN([Run Report],Full List Table[Run Report]))

Thanks

Phil