4 Replies Latest reply: Nov 30, 2015 8:19 AM by Andy Faulkner RSS

    where exists

    Andy Faulkner

      Hello

       

      I have two tables that are concatenated and build tableAB:

      tableA

      tableB

      I have three others:

      tableC

      tableD

      tableE

      tableC is the parent and is inner joined to both  tableD, tableE

       

      I would like tableC to only contain records found in tableAB (inner join).

       

      I've tried where exists: Load tableC where exists(tableAB.column,tableC.column) but the scripting results report 0 records from tableC, however tableD and tableE still get loaded.

       

      Can someone tell me where Im going wrong and how D and E load while inner joined to a table that apparently has zero records in it?

       

      Thank you.

        • Re: where exists
          Massimo Grossi

          Please post the script, thx

          • Re: where exists
            Andrew Walker

            Try

             

            (this is pseudocode)

             

            C:

            Left Keep(AB)

            Load

            *

            From SourceTableC;

             

            AB is a resident table and you want to create a new resident table C. In a Left Keep script like this the data from SourceTableC is reduced down to the rows with that match those in AB in the fields of the same name. Like a Join but the tables remain separate in the data model.

             

            An exists clause is good but only operates on a pair of fields (one in each of the tables that can have different names). Keep can operate on many pairs of like named fields.

             

            In the scenario above a Left Keep will reduce data coming from the source. A Right Keep (not so common) will bring in all records from the source but reduce the resident table to the matching rows. An Inner Keep reduces both to their matching rows.

             

            If you're thinking that keep is just like a join then you're dead right. But there is that big difference - the tables remain separate in the model.

            • Re: where exists
              Stefan Wühl

              Are you qualifying your tables?

               

              Then I think you need to take care of the field name in the EXISTS() since the second qualify has not happened when loading in the data:

               

              QUALIFY *;

               

              TableAB:

              LOAD * INLINE [

              Column

              1

              2

              3

              ];

               

              TableC:

              LOAD * INLINE [

              Column

              3

              5

              6

              ]

              WHERE EXISTS(TableAB.Column, Column)

              ;

              • Re: where exists
                Andy Faulkner

                thank you for your replies, I have something to go with now.