Remove Duplicate values with a condition
pmulroney at logicaldevelopments.com.au
Tue Sep 13 07:38:10 UTC 2022
Glad it's what you're looking for! Just to be clear, $ListIntersect() isn't a built-in function of Omnis, we had to write it!
Hopefully the github project will make sense of what I'm trying to say.
> On 13 Sep 2022, at 3:32 pm, Martin Obongita <martin.obongita at yahoo.com> wrote:
> 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 <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.
>> On 13 Sep 2022, at 2:43 am, Martin Obongita via omnisdev-en <omnisdev-en at lists.omnis-dev.com <mailto: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
>> #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 <mailto:dlr at futurechalk.com>> wrote:
>> 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 <mailto: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 <mailto: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.
>>>> On 12 Sep 2022, at 4:52 am, Martin Obongita via omnisdev-en <omnisdev-en at lists.omnis-dev.com <mailto: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 <https://lists.omnis-dev.com/>
>>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com <mailto:omnisdev-en at lists.omnis-dev.com>
People used to laugh at me when I would say "I want to be a comedian", well nobody's laughing now.
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