Custom function to find duplicates

[kbernstein]

In various places there is outstanding available custom functions that eliminate duplicate values in a list of records. I am trying to do something a little different, leaving me a bit stymied.

I would like to use a custom function to loop through a found set of records and detect a duplicate value in a text field. The result of the custom function would be a listing of those values that are duplicate. If There are no duplicates the result would be blank.

Lets say I have a found set of 5 records

Apple

Pear

peach

Pear

Grape

I am looking for a result that shows:

NotADup

Pear

NotADup

Pear

NotADup

I want the custom function to compare itself to every other found record (except itself) to test for a duplicate.

Here is my flawed CF as it stands now:

Let([

TotalNum = Found;

CurrentNum = Found;

TestNum = Found;

CurrentRecord = GetNthRecord(Field;$CurrentNum);

TestRecord = GetNthRecord(Field;$TestNum)];

Case(

CurrentNum < 1;

Case(

TestNum < 1;

Found & DupCheck(Field;CurrentNum - 1);

CurrentNum=TestNum;

DupCheck(Field;TestNum-1);

CurrentRecord = TestRecord;

CurrentRecord & "¶"& DupCheck(Field;TestNum-1);

CurrentRecord <> TestRecord;

"NotADup" & "¶"& DupCheck(Field;TestNum-1);

CurrentRecord

)))

The problem as I see it; using the recursive nature of CF I can only advance one field at a time. I have no problem advancing the "TestNum" variable, but at the end of the first loop, I need to reset the "TestNum" variable back to "Found" and the "CurrentNum" variable to Found - 1. How can I change two variables at one time?

Any assistance would be appreciated, editing this logic or taking a new approach to resolving my DeDuping issue.

Thank you.

[comment]

I'm afraid your CF is unreadable, since it references many undefined things. It's also not quite clear what the intended result is. First you say:

"a listing of those values that are duplicate. If There are no duplicates the result would be blank."

But then you show an example result with placeholders for unique values:

NotADup

Pear

NotADup

Pear

NotADup

In any case, I believe you should do this in two stages: first, compile a list of all values in the field. In the second stage, go over each value and check if it appears more than once in the list; if yes, write it out, if not, move on (or write out a placeholder?).

[David Jondreau]

Well, I just posted this on TT, so I might as well post it here too.

I agree with comment, don't use GetNth in the function itself. Load all the values into the parameter. GetNthRecordSet is a good CF for doing that (Check www.briandunning.com)

There's probably a more elegant way and I haven't tested it a whole lot, but I think this will get you there...

KeepDupes (text;counter)

//So the text parameter in this function would be GetNthRecordSet(table::field;1;10000)

//counter should start at 1

//unique can be changed to "NotADup" or an empty value or whatever you want

Let([

unique = "unique";

ValCount = ValueCount(text);

pilcrow = Case(counter < valCount; ¶) ;

thisVal = GetValue(text; counter);

UniqueTest = Case( PatternCount(¶ & text & ¶ ; ¶ & thisVal & ¶) =1 ;1;0);

thisValueResult = Case(uniqueTest; unique; thisVal) & pilcrow;

recurse = (counter ≤ ValCount);

result = thisValueResult & Case( recurse ; KeepDupes(text; counter +1)

)];

result

)

PS: You may want to check your profile. I assume you're not using "FM 6 Dev".

Edited April 3, 2008 by Guest

[Søren Dyhr]

This:

UniqueTest = Case( PatternCount(¶ & text & ¶ ; ¶ & thisVal & ¶) =1 ;1;0);

... could be written like this:

My reasoning is that Case( has a larger overhead, and then might be a tad slower to evolve, than a more bit near function.

--sd

UniqueTest = not Abs(PatternCount(¶ & text & ¶ ; ¶ & thisVal & ¶) -1)

[comment]

I agree with comment, don't use GetNth in the function itself.

On second thought, it could be done all in one pass:

DuplicateValues ( fieldName ; start ; end ; register ; result )

Let ( [

this = GetNthRecord ( fieldName ; start ) ; 

nextRegister = register & this & ¶ ;  

nextResult = result & Case ( not IsEmpty ( FilterValues ( this ; register ) ) and IsEmpty ( FilterValues ( this ; result ) ) ; this & ¶ )

] ;

Case ( 

start < end ; 

DuplicateValues ( fieldName ; start + 1 ; end ; nextRegister ; nextResult ) ; 



FilterValues ( nextRegister ; nextResult ) 

)

)

To call the function:

DuplicateValues ( YourFieldName ; 1 ; Get (FoundCount) ; "" ; "" )

I'm still curious what this might be good for, though.

[Søren Dyhr]

I'm still curious what this might be good for, though

Same here, why mimic a single line step Perform Find with an exclamation mark inside. While I on the other hand can see points in reducing the length of more than single liners?

But otherwise ...to the tail recursion!

--sd

[Søren Dyhr]

Metaphoric reluctance???

--sd

[LaRetta]

Are you talking to yourself again, Soren? :content:

[Søren Dyhr]

No just Hemmingway'ing an old post of mine:

http://fmforums.com/forum/showpost.php?post/261818/

The post I replied to did vanish into thin air! Your's I think ... perhaps you can enlighten me of obvious purpose, you suddenly realized existed???

--sd

[LaRetta]

Well, I was on drugs; what can I say? Yep, post was mine and I was just wondering if anyone considered performing find with ! or even simple relationship. But then ... I realized who the respondees were and knew they would have considered that if it would have been helpful. So I deleted my post and went back to bed where my mind can fly these answers as I fly and where my answers are ALWAYS right.

I was teasing you because I could ... :girlgiggle:

[Søren Dyhr]

Indeed a naughty way to make me look daft!

However can we not be absolutely sure that kbernstein, not is just as accomplished as Don Wieland, and thereby take care of his own blunders.

http://www.fmforums.com/forum/showtopic.php?tid/194325/tp/0/all/1/

--sd

[LaRetta]

I'm nowhere near that level but I know that I've spent days on something when someone would say, "why not do this" and solve it in 1 second. I say, "Well butter my butt and call me a biscuit!!" I adore being humbled. 8*]

[Vaughan]

"I adore being humbled."

And having your butt buttered.