Remove Duplicate values with a condition

Paul Mulroney pmulroney at logicaldevelopments.com.au
Tue Sep 13 02:48:14 UTC 2022


Hi Martin,

Sounds like you need to do a list intersection.  We developed a method of optimised selecting matches in one list for values that appear in another list.  We put the Studio 5 version up on Github:

https://github.com/pmulroney/ListIntersect_for_Studio5 <https://github.com/pmulroney/ListIntersect_for_Studio5>

That might achieve what you want - select the matches in one list, invert the selection and then merge into your final list.

Regards,
Paul.


> On 13 Sep 2022, at 2:43 am, Martin Obongita via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
> 
> Hi Dan Ridinger,
> I appreciate your help.
> I have done the following:
> Do iSQLTableColNameRowList.$merge(lSQLTableColNameRowPrepareList,kTrue,kTrue,kFalse)
> Do iSQLTableColNameRowList.$sort($ref.lOriginalColName)
> 
> Do iSQLTableColNameRowList.$cols.lOriginalColName.$selectduplicates(kTrue)
> 
> Do lSQLTableColNameRowSelectList.$copydefinition(iSQLTableColNameRowList)
> 
> Do lSQLTableColNameRowUnselectList.$copydefinition(iSQLTableColNameRowList)
> 
> Calculate lLineCount as iSQLTableColNameRowList.$linecount
> For lLineNum from 1 to lLineCount step 1
> If iSQLTableColNameRowList.[lLineNum].$selected
> If iSQLTableColNameRowList.lIsSelect=kTrue
> Do lSQLTableColNameRowSelectList.$add(,iSQLTableColNameRowList.lColName)
> End If
> Else
> #not a duplicate
> Do lSQLTableColNameRowUnselectList.$add(iSQLTableColNameRowList.lColName)
> End If
> 
> End For
> 
> The list iSQLTableColNameRowList has rows with the column "lIsSelect" boolean status both true and false.
> But after running the method, lSQLTableColNameRowSelectList does not contain any data.
> All the data is added to lSQLTableColNameRowUnselectList both true and false "lIsSelect"
> I tried using $filter but I got no better result either.
> WHAT I AM DOING: I am merging two lists. List2 contains some data from List1.
> List1 has "lIsSelect" column set to false for ALL data. List2 has "lIsSelect" set to true for some data.
> I need to merge List1 with the boolean flag status from List2.
> 
> CONCLUSION: I just need to know how to select the FIRST duplicate record in a list.
> Then I can remove that first row.
> Because by default, the $selectduplicates function will select the SECOND duplicate record.
> Kind regards,
> Martin O.     On Monday, September 12, 2022 at 08:19:43 PM GMT+3, <dlr at futurechalk.com> wrote:  
> 
> Hello,
> 
> If you have some way to determine in the selected list the duplicates you wanted you could use $filter to get the rows. Otherwise you would have write a loop to get the first row at the control break of the sorted column.
> 
> Dan Ridinger
> 
>> On Sep 12, 2022, at 10:00 AM, Martin Obongita via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
>> 
>> Hi Paul,
>> I have played around with this duplicate.
>> There is this command: $selectduplicates.
>> Using this command will select the second duplicate row.
>> Is there a way to set the command to select the first duplicate row?
>> Kind regards,Martin O.
>> 
>>     On Monday, September 12, 2022 at 03:24:37 AM GMT+3, Paul Mulroney <pmulroney at logicaldevelopments.com.au> wrote:  
>> 
>> Hi Martin,
>> 
>> I had to do this recently where I wanted to remove any duplicates the combination of two fields - first name and last name.
>> 
>> This is how I did it (Studio 10.0, but should work anywhere).  Note I had to create a local var vsFullName
>> 
>> # Remove duplicates - same first name and surname
>> Do vlOutput.$sort($ref.LastName,kFalse,$ref.FirstName,kFalse)
>> Do vlOutput.$cols.$add(vsFullName)                                              
>> Do vlOutput.$sendall($ref.vsFullName.$assign(con($sendallref.FirstName,' ',$sendallref.LastName)))      ## Create special column to remove dups
>> Do vlOutput.$cols.vsFullName.$removeduplicates(kTrue,kTrue)
>> 
>> You might need to do something similar.
>> 
>> Regards,
>> Paul.
>> 
>> 
>>> On 12 Sep 2022, at 4:52 am, Martin Obongita via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
>>> 
>>> Hi ALL,
>>> I have the method below to remove duplicate rows in a listDo iSQLTableColNameRowList.$sort($ref.lOriginalColName,kFalse)
>>> Do iSQLTableColNameRowList.$cols.lOriginalColName.$removeduplicates()
>>> The problem is it deletes all duplicate rows.
>>> How do I remove duplicate rows in a list with a condition, say, remove all duplicates but check that C7(the column flag in the list) is true?
>>> Kind regards
>>> _____________________________________________________________
>>> Manage your list subscriptions at https://lists.omnis-dev.com
>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
>> 


The truth is out there. Anybody got the URL?
(Geeky One Line Jokes http://bit.ly/XU8lFD)
-- 
Paul W. Mulroney                                            We Don't Do Simple Pty Ltd 
pmulroney at logicaldevelopments.com.au       Trading as Logical Developments
www.logicaldevelopments.com.au                   ACN 161 009 374 
Ph: +61 8 9458 3889                                       86 Coolgardie Street
                                                                         BENTLEY  WA  6102





More information about the omnisdev-en mailing list