Remove Duplicate values with a condition

Martin Obongita martin.obongita at yahoo.com
Tue Sep 13 07:32:00 UTC 2022


 Hi Paul
Without a doubt, this is exactly what I was looking for.
I didn't know it is called list intersect different from list merge.
Thank you so much.I feel like a HUGE load has been lifted from my shoulder.
Kind regards,Martin O.
    On Tuesday, September 13, 2022 at 05:48:23 AM GMT+3, Paul Mulroney <pmulroney at logicaldevelopments.com.au> wrote:  
 
 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
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