6 Replies Latest reply on Apr 27, 2016 9:11 AM by electon

    Self-Join Table Not Getting Expected Results

    Linda

      I have created a self-join file to identify records where DateMailed does not equal VisitDate.  When I display the records from this self-join, I am seeing the same record over and over and the VisitDate and DateMailed match. This is my first time using a self-join.  Is there something else I need to do to get the self-join to only display records where the two dates in the same record don't match?  thanks, Linda

       

        • 1. Re: Self-Join Table Not Getting Expected Results
          erolst

          What do you mean by “identify”? Find records where the two dates are ≠?

           

          In that case, add

           

          Visits::primaryKey = Visits_self::primary

           

          as an additional predicate to the relationship definition. Now simply use the script step

           

          Go to Related Records [ from Table: Visits_self ; use current layout ; matching only ; match entire found set ]

           

          from the context of Visits – or just search for records where Visits_self::primaryKey isn't empty; add the related field on the layout and search for * (Asterix asterisk).

          • 2. Re: Self-Join Table Not Getting Expected Results
            electon

            It depends strongly on the context and where you place you related data on the layout.

            The fact that you see the same record over points out that this might be your problem.

             

            Can you explain how you select the left side record and display the right side related data?

            Or better a screenshot of the layout.

            • 3. Re: Self-Join Table Not Getting Expected Results
              Linda

              What I am trying to do is find all records in my Visits table where the Visits::DateMailed and the Visits::VisitDate are not equal.  My search on the web lead me down the path of a self-join, where I have joined 2 instances of the same file on the two different date fields with a not equal between the two.  Obviously, I don't understand what I am doing! I appreciate all the help.

               

              Here is the join:

               

               

              Here is report:

               

               

              Here is layout using the self-joined table:

               

              • 4. Re: Self-Join Table Not Getting Expected Results
                electon

                By far the easiest will be to create another calculation field that flags the records:

                Let's take this example for the calculation:

                 

                If ( VisitDate <> DateMailed ; 1 ; "" )

                 

                Then all you do is Perform Find on that field with a criteria of 1

                 

                Enter Find Mode []

                SetField [ theNewFlagField ; 1]

                Perform Find

                • 5. Re: Self-Join Table Not Getting Expected Results
                  Linda

                  Thank you, electon, I just tried that and it worked great.  Very simple solution.

                   

                  Thank you, erolst, I am going to try out your method, as well, because I really want to understand the self-join. 

                   

                  thanks so much.  I'll post back shortly.

                   

                  Linda

                   

                  PS - how do I show that two answers in this post are the "correct" answers?

                  • 6. Re: Self-Join Table Not Getting Expected Results
                    electon

                    Don't think you can. You can mark as helpful of correct.

                    I kinda like erolst solution better because it doesn't need an extra field.

                    But do like the flag because it's very simple and purpose oriented.

                     

                    Whichever suits you best.