12 Replies Latest reply on Dec 20, 2013 8:08 AM by Oliver_Reid

    Trying to report unique instances of duplicates after constraining find

    savingprivatedata

      Trying to report unique instances of duplicates after constraining find

      I have the following structure;

       

      FIELDS;

      UniqueID

      Equip_ID

      Location

      TestNumber

      TestDate

       

      This table records each test number that a piece of equipment performs, along with the date of the test and the location.

       

      There are often multiple tests per piece of equipment on a given day.

      I'm trying to report the following;

      1) Multiple instances of a Equip_ID in a given time period.

      2) Locations transmitting from Equip_ID in a given timer period.

       

      I want to be able to take this;

       

      Equip_ID | Location | TestNumber | TestDate

      1 NewYork 111 10-5-2013

      1 NewYork 112 10-5-2013

      1 NewYork 113 10-5-2013

      2 Utah 245 10-5-2013

      3 California 345 10-6-2013

      3 California 346 10-6-2013

      4 Oklahoma 400 10-6-2013

      4 Oklahoma 401 10-6-2013

      5 Florida 405 10-7-2013

      6 Illinois 450 10-7-2013

      6 Illinois 451 10-7-2013

      6 Illinois 452 10-7-2013

      7 NewYork 500 10-7-2013

      7 NewYork 500 10-7-2013

      8 Florida 530 10-8-2013

      8 Florida 531 10-8-2013

      1 NewJersey 575 10-10-2013

       

      And display single instances of LOCATIONS that transmitted tests from more than one Equip_ID;

      For the month of Oct.2013;

      Equip_ID | Location | TestDate

      1 NewYork 10/2013

      7 NewYork 10/2013

      5 Florida 10/2013

      8 Florida 10/2013

       

      AND

       

      Display Equip_ID that transmitted from multiple locations

      For the month of Oct.2013;

      Equip_ID | Location | TestDate

      1 NewYork 10/2013

      1 NewJersey 10/2013

        • 1. Re: Trying to report unique instances of duplicates after constraining find
          erolst

          1. Create a self-relation by location and month and a calculation field as Count ( selfJoin_byLocMonth::primaryID )*. Look for records where the field > 1. If you want to see single instances, create a summary layout for the found set.

           

          2. Create a self-relation by equipID and month, sorted by location, and a calculation field as Count ( selfJoin_equipMonth::primaryID ) > 1 and selfJoin_equipMonth::location ≠ Last ( selfJoin_equipMonth::location ). Look for records where the result is 1.

           

          *EDIT: first calculation must be: Count ( selfJoin_byLocMonth::primaryID ) and selfJoin_byLocMonth::equipment ≠ Last ( selfJoin_byLocMonth::equipment )

          1 of 1 people found this helpful
          • 2. Re: Trying to report unique instances of duplicates after constraining find
            savingprivatedata

            UPDATE;

            If you could be a little more specific, I'd appreciate it.  I tried doing what you said, and it doesn't actually show me only single instances of Equip_ID duplicates per month. I'm sure I've misinterpreted your post, but I'm getting what looks like all, or most of the equip instances.

            Keep in mind my example;  Many, if not all of the Equip_ID will have multiple tests in a month.  Each test number is different, so the record isn't a true dupe.  Because of this, Sub-summary reports tend to show me everything.  I only need to show what I've showed in the example, above.

            When I first attempted this, the first thing I tried was the sub summary route, but it never worked because I need to overcome the multiple tests, THEN narrow down to only single instances of Equip_ID that has tests from multiple stores in a month.

            And of course, single instances of stores that have multiple Equip_ID in a month.

            Again, thanks!

             

            erolst,

            Thanks so much for the response.  I'm confused regarding;

            Count ( selfJoin_byLocMonth::primaryID )

             

            The actual table I'm working from is called "WeeklyReport"

             

            Should this be;

            Count (WeeklyReport::UniqueID )?

             

            I'll give it a shot to see what happens.  Sorry for the stupid question.  I'm learning.

            Thank you!

            • 3. Re: Trying to report unique instances of duplicates after constraining find
              erolst

              Yeah, well, I didn't say it's easy …

               

              Anyway, my first suggestion was incomplete, both calculations should use the same idea (isolate & compare). See if this helps you:

              • 4. Re: Trying to report unique instances of duplicates after constraining find
                savingprivatedata

                erolst,

                LOL!  Thanks so much!  Apparently, I have to really focus on relationships and sub-summary reports.

                I'm not sure HOW this is working, so I need to go back and look under the hood for awhile. 
                Three instances of the main table, with counts.  I've got some studying to do, tonight.

                All I need now, is to whittle it down to show only the multiple instances. 

                Thanks again, I've been trying to make this happen for weeks!

                • 5. Re: Trying to report unique instances of duplicates after constraining find
                  savingprivatedata

                  I'd like to display only Equip at multiple locations in a month instead of all the equip for that month.  After the data is condensed from the sub-summary, I've still got about 100,000 lines to scroll through.

                  To achieve this last step, should I post it to another question to be solved or should I continue here?

                  • 6. Re: Trying to report unique instances of duplicates after constraining find
                    Datagrace

                    I'd stick with this thread. At the top of the thread, you state the basic setup, and it gets confusing if readers have to look at another thread to get the picture.

                    • 7. Re: Trying to report unique instances of duplicates after constraining find
                      erolst

                      I suggest you first search for the month, then *constrain* the resulting found set to records where equipInMultipleLocation? = 1.

                       

                      The first search whittles down your obviously massive amounts of data by searching in a stored (or storable) field or fields, the second one use a non-storable field, but operates on a much smaller found set.

                       

                      If you have tremendous amounts of data, convert the two calculation fields into regular number fields and have a script calculate and set the results on import (I assume you don't type in those figures by hand …). Now all your search fields are storable, which will speed up the search.

                       

                      savingprivatedata wrote:

                      I've still got about 100,000 lines to scroll through.

                       

                      And keep in mind that you don't need a body part if you're only interested in the summary results.

                      • 9. Re: Trying to report unique instances of duplicates after constraining find
                        savingprivatedata

                        Thanks, erolst!  I'm not sure how I'd implement equipInMultipleLocation?

                         

                        Currently, my found set has close to 90,000 results for just the month of Nov. 

                        Even though your attachment consolodated multiple instances of Equip_ID into single instances, I still have to scroll through thousands of rows to find 3 or 4 where there are multiple locations for a single Equip_ID.

                        My results look like this;

                        (Except it's actually thousands of rows)

                        I'd like to show ONLY the Equip_ID with multiple Locations.
                        Equip_ID  |  Location  |  Date

                        36794  |  0614  |  11/2013

                        36795  |  0256  |  11/2013

                        36796  |  0778  |  11/2013

                        36797  |  4331  |  11/2013

                        36797  |  0287  |  11/2013

                        36798  |  0024  |  11/2013

                        36799  |  8788  |  11/2013

                        36800  |  3455  |  11/2013

                        36801  |  0074  |  11/2013

                        36802  |  0364  |  11/2013

                        36803  |  6321  |  11/2013

                        • 10. Re: Trying to report unique instances of duplicates after constraining find
                          erolst

                          savingprivatedata wrote:

                           

                          Thanks, erolst!  I'm not sure how I'd implement equipInMultipleLocation?

                           

                          By creating it as a calculation field, as in my sample file?

                           

                          This gives you a field that indicates if an equipment has multiples locations, as per the calculation, and I suggested to *first* find by Month/Year, then constrain to records with this flag.

                           

                          In practice, this means the find for month/year gives you 90,000 records, and the subsequent constrain to record where equipInMultipleLocation? = 1 will whittle that down to all those that have multiple locations.

                          • 11. Re: Trying to report unique instances of duplicates after constraining find
                            Oliver_Reid

                            Here is an alternative that uses simple Execute SQL quiries, although it does involve adding 4 repeating fields and result calculation:

                             

                            rep1

                             

                            Let($r=Get ( CalculationRepetitionNumber );

                            Substitute(MiddleValues(ExecuteSQL("select  Equip_ID, Location from myTable group by location, Equip_ID ";",";¶) ; $r;1);¶;"" ) )

                             

                            rep 2   (collects just the location -- othewise the same)

                             

                            Let($r=Get ( CalculationRepetitionNumber );

                             

                            Substitute(MiddleValues(ExecuteSQL("select  Location from myTable group by location, Equip_ID ";",";¶) ; $r;1);¶;"" ) )

                             

                            rep3      (identifies duplicate location/equip_id values)

                             

                            Let($n=Get ( CalculationRepetitionNumber );

                             

                            Case(rep2  = "";0;

                            rep2[$n-1] ≠ rep2 and rep2[$n+1] ≠ rep2 ;0;

                            1)

                            )

                             

                            rep4      (filters rep1 using rep 3)

                             

                            If(rep3;rep1)

                             

                             

                            Result

                             

                            list(rep4)

                             

                            =  

                             

                            5,Florida

                            8,Florida

                            1,NewYork

                            7,NewYork

                             

                             

                            It would be possible for me do to this is one step if Execute SQL allowed a subquery after the FROM, or allowed unstored fields to be used in a Where clause.

                             

                            Maybe someone else could take up the challenge!

                            • 12. Re: Trying to report unique instances of duplicates after constraining find
                              Oliver_Reid

                              To restrict to one month, create a "test_month" field based on the date, and a global for the search month. Then add

                               

                              test_month=?  

                              to the where clauses,

                               

                              using the global field as the parameter for the ?.