6 Replies Latest reply: May 15, 2014 10:08 AM by Lori S RSS

    <<The New Guy>> Deleting Duplicates with a date

    guyfromri _

      Hey Guys! I'm new here so I figure I'll open with something simple. I'm using Monarch to pull data from a txt file that is formatted like a report and I have duplicates. I'm ok with filters and calculating fields but this is a bit advanced for me.

       

      I'm trying to delete duplicates(which I can do) but I have column of dates and I want to keep the most recent date.

       

      So basically, I have a ton of columns but I'm only using one (A Chart Number) to identify my dups and I could have anywhere between 2 and 20 of any chart number. Once the duplicates are gone, I want to keep the most recent occurence of that chart number based on a date column(LastVisit). I've tried some things with "Max"[/code] and some date>date[/code] but that doesn't do the trick. It's probably pretty simple but I can't seem to find anything on google about it.

       

      Any advice or if someone could even point me in the right direction, that would be awesome!

       

      Thanks in advance!!

        • <<The New Guy>> Deleting Duplicates with a date
          Data Kruncher

          Hi guyfromri, and welcome to the forum!

           

          As you have v10, this'll be easier for you than it is for others.

           

          First, in the Table window, define a custom sort order based first on the chart number, and then on the date field (descending).

           

          Now define a new filter. Name it No Duplicates and instead of messing with the formula, go straight to the Advanced tab.

           

          Click the "Select row according to ..." option, and check the duplicated rows box, with the first row only, as sorted. Check the box for the chart number on the right hand side of the dialog, and OK the filter and OK again for the list of filters.

           

          How's that?

          • <<The New Guy>> Deleting Duplicates with a date
            guyfromri _

            Hi Data Kruncher! Thanks for the reply.

             

            Sadly, in my haste while signing up for the forum, I put "10" and I really have 7. I don't know why I did that....I imagine that this makes this unapplicable?

             

            Thanks for the help, at any rate!

              • <<The New Guy>> Deleting Duplicates with a date
                Lori S

                Have you tried using a Summary to get the single occurence?

                  • <<The New Guy>> Deleting Duplicates with a date
                    guyfromri _

                    Thanks Lori!! Could you elaborate a bit? I don't have any formal training with Monarch so everything I do is a learning experience.

                     

                    Thanks for the input!

                      • <<The New Guy>> Deleting Duplicates with a date
                        Data Kruncher

                        If you have v7 "Pro" then I have a possible solution. If you don't then I'm afraid that I've come up empty.

                         

                        First, add a calculated field named RecNo using the RecNo() function to your table.

                         

                        Now sort the table by Location first (ascending), and then by LastVisit (descending).

                         

                        Build a project export to export the entire table to a file (.xls, .xlsx, .mdb, etc.). Save the model and project.

                         

                        Now create a new model/project, using that export as a datasource. Add to it an external lookup (to the same file[/B]) based on the Location fields. Import the RecNo field as LocationRecNo. You'll get a warning about not forming a unique key. That's OK. Click Yes and proceed.

                         

                        Build a filter with the expression:

                        RecNo=LocationRecNo[/CODE]

                         

                        This should give you the unique list in the sequence that you want. Save the model and project when it's correct.

                          • <<The New Guy>> Deleting Duplicates with a date
                            Lori S

                            My method is a little simpler.

                             

                            Open the Summary window (Window/Summary).

                            On the Summary Start Page, select New Summary - Complete.

                            On the General tab, name the summary and apply a filter if needed (if you don't specify one, it will default to what ever is in the table window).  There's a checkbox if you want a total line.

                            On the Fields tab, select the fields that have the duplicate values and put them into the Key or Item fields.

                            Monarch will summarize the data based on what fields you specify.

                             

                            Make sense?