1 2 3 Previous Next 32 Replies Latest reply on Aug 4, 2017 12:31 AM by mainframe

    Find non-duplicates of related records

    mainframe

      I have 2 tables

       

      TABLE1         related to                                                                     TABLE2                                         

      NAME1.a                NAME1                                                                NAME1.a                NAME1              ID1

                                                                                                                  NAME1.a                NAME1              ID2

                                                                                                                  NAME1.a                NAME1              ID3

                                                                                                                  NAME1.a                NAME1              ID4

       

      NAME1.b                NAME1                                                                NAME1.b                NAME1              ID1

                                                                                                                  NAME1.b                NAME1              ID2

                                                                                                                  NAME1.b                NAME1              ID5

                                                                                                                  NAME1.b                NAME1              ID6

                                                                                                                  NAME1.b                NAME1              ID8

                                                                                                                  NAME1.b                NAME1              ID9

       

      NAME1.c                NAME1                                                                NAME1.c                NAME1              ID1

                                                                                                                  NAME1.c                NAME1              ID2

                                                                                                                  NAME1.c                NAME1              ID3

                                                                                                                  NAME1.c                NAME1              ID4

       

       

      'NAME1.a' field in TABLE1 is related to 'NAME1.a' field in TABLE2 etc.

      The first qualifier e.g. NAME1 is stored in a second field in both tables.

      I want to find out based on NAME1 field (not NAME.a,b,c etc) which related sets are not identical.  In this case that would be 'NAME1.b' would not be identical. All tables have unique values apart from 'NAME1'

       

      what is the smartest way to obtain the desired result telling that NAME1.b is not equal ?

        • 1. Re: Find non-duplicates of related records

          How is the output intended to be displayed? in a list format contained within a single field ,via portal, report?

           

           

          Could you create another Table Occurance and relate the Name1 field to to this new TO then use portal?

          Maybe use the execute(SQL) function using WHERE .... ?

          • 2. Re: Find non-duplicates of related records
            mainframe

            Thanks Brian

             

            I prefer to have it list format as it is ... an SQL could be an option ... no portals at all

            we are talking of 10000 entries in table1 and average 40000 in table2. 

            • 3. Re: Find non-duplicates of related records

              I'm sorry  I just reread the original question and i think I may have misunderstood what you were asking in the OP. Not to mention the example you provided is messed up on my screen due to formatting differences.

               

              Can you provide a sample file with just a few sample entries  or if no personal info is involved  screenshots of  the two tables and the fields.

              • 4. Re: Find non-duplicates of related records
                fmpdude

                Those kinds of record counts are nothing for a true SQL environment, but with any kind of GROUP BY, FMP will likely take a long time to complete. Not sure without testing, naturally, so as suggested:

                 

                1. Please post a representative data set for each table.

                2. Include the expected output.

                 

                Doesn't have to be a lot of data, just enough to unambiguously demonstrate whatever you need the output to be.

                 

                From what I read so far, I'm thinking this may be a simple SUB-SELECT SQL or something similar.

                • 5. Re: Find non-duplicates of related records
                  mainframe
                  NAMEHLQID
                  AKD.**AKDA
                  AKD.**AKDB
                  AKD.**AKDC
                  AKD.**AKDD
                  AKD.SAKDLOADAKDA
                  AKD.SAKDLOADAKDE
                  AKD.SAKDLOADAKDB
                  AKD.SAKDLOADAKDC
                  AKD.SAKDLOADAKDD

                   

                  Sorry  HAD TO DO IT WITH EXCEL

                   

                   

                  the 2 tables are self joined based on HLQ and ID.  The Portal setup shows the number of occurrence . For 'ID E = 1' and all other are '2'  which is correct. The numbers could be much higher than just 2.

                   

                   

                  But I want to show in list form only those HQL & ID items where the e.g. portal count is the same for all rows.

                  How could I define the Portal count in a calculation   ?

                  • 7. Re: Find non-duplicates of related records
                    mainframe

                    thanks for the reminder.

                     

                    I did insert readable items

                    • 8. Re: Find non-duplicates of related records

                      Here is one Option,  I made a Count(ID) field to give the number of records found in your self-join relationship (the portal count)  then I created a second calc field  with  the following eSQL

                       

                      ExecuteSQL ( "Select Distinct ID FROM test WHERE HLQ=? AND C_Count=?"; "" ;"¶"; HLQ; C_COUNT)

                       

                      the output display of this field yields something to the effect of

                      Capture.JPG

                      • 9. Re: Find non-duplicates of related records
                        mainframe

                        many thanks Brian

                         

                        in my case a SQL is of no help as for detailed analyses it has to be in list form to decide about further actions.

                        Hence using self-joined tables have to be used. This works - however it is a very slow process for 40000 records.

                        • 10. Re: Find non-duplicates of related records
                          fmpdude

                          Slow process?

                           

                          40,000 records SQL should be nearly instantaneous.

                           

                          In MySQL, for example, a 1,000,000 GROUP BY query takes 0.5 seconds. OK, FMP is not a SQL Database (whatever that means given all the SQL support, SQL Reference Manual, FMP JDBC driver, the lack of any SQL disclaimers in any FMI reference documentation, etc.), but 40,000 record queries should be rocket fast.

                           

                          Can you post your data (or create some similar data with a free tool like RANDOMMITE)?

                          • 11. Re: Find non-duplicates of related records
                            mainframe

                            Thanks

                            I will provide you with more details ASAP.... takes 1-2 weeks as I am away

                            • 12. Re: Find non-duplicates of related records
                              fmpdude

                              Take your time. I'll be glad to help if I can.

                              • 13. Re: Find non-duplicates of related records
                                mainframe

                                before I am away here a table:

                                         

                                NAMEsame_NAMEunique_NAMESHLQAUTH_IDACCESSsame_ACCESSnot_same_ACCESS
                                MAXI.**37MAXIXXXESM01ALTER714
                                MAXI.**37MAXIXXXESM04UPDATE714
                                MAXI.**37MAXIXXXOS001ALTER714
                                MAXI.MAINT1.A.APFLOAD37MAXIXXXESM01ALTER714
                                MAXI.MAINT1.A.APFLOAD37MAXIXXXESM04UPDATE714
                                MAXI.MAINT1.A.APFLOAD37MAXIXXXOS001ALTER714
                                MAXI.MAINT1.A.LOAD37MAXIXXXESM01ALTER714
                                MAXI.MAINT1.A.LOAD37MAXIXXXESM04UPDATE714
                                MAXI.MAINT1.A.LOAD37MAXIXXXOS001ALTER714
                                MAXI.MAINT1.B.APFLOAD37MAXIXXXESM01ALTER714
                                MAXI.MAINT1.B.APFLOAD37MAXIXXXESM04UPDATE714
                                MAXI.MAINT1.B.APFLOAD37MAXIXXXOS001ALTER714
                                MAXI.MAINT1.B.LOAD37MAXIXXXESM01ALTER714
                                MAXI.MAINT1.B.LOAD37MAXIXXXESM04UPDATE714
                                MAXI.MAINT1.B.LOAD37MAXIXXXOS001ALTER714
                                MAXI.MAINT2.A.LOAD37MAXIXXXESM01ALTER714
                                MAXI.MAINT2.A.LOAD37MAXIXXXESM04UPDATE714
                                MAXI.MAINT2.A.LOAD37MAXIXXXOS001ALTER714
                                MAXI.MAINT2.B.LOAD37MAXIXXXESM01ALTER714
                                MAXI.MAINT2.B.LOAD37MAXIXXXESM04UPDATE714
                                MAXI.MAINT2.B.LOAD37MAXIXXXOS001ALTER714

                                 

                                 

                                The HLQ MAXI points to this related table in LIST format.  Each  'group of names e.g. MAXI.**' must be matched to the 'other group of NAMES e.g.  MAXI.MAINT1.A.APFLOAD; MAXI.MAINT1.A.LOAD etc'.  or vice versa naturally.

                                 

                                In above sample, all items related to HLQ MAXI have the same 'AUTH_ID and ACCESS' anything else would be wrong.

                                That means, there could be additional rows pertaining to HLQ MAXI having other MAXI.xxxx NAMEs with more or less rows and/or the AUTHID_ID and or just the ACCESS could be different.

                                 

                                 

                                Above we have 7 unique NAMES and the same NAME appears 3 times.

                                 

                                So my question is what is the simplest way to find out that all 'group of NAMES' are euqal.

                                 

                                many thanks

                                • 14. Re: Find non-duplicates of related records
                                  fmpdude

                                  Sorry, I don't quite understand what you mean by "....what is the simplest way to find out that all 'group of NAMES' are euqal.".

                                   

                                  So that I and others can follow you, can we try to do a simpler example that's easier to follow?

                                   

                                  How about something like CUSTOMER and ORDER?

                                   

                                  Create a simple table for CUSTOMER and another table for ORDER

                                   

                                  Put in some basic data.

                                   

                                  Describe what you want to do in those terms.

                                   

                                  If the 1:M CUSTOMER and ORDER isn't right for your example, then change it to be something simple like that. Once you do, everyone can (and will) jump in and help.

                                  1 2 3 Previous Next