6 Replies Latest reply: Jan 13, 2012 3:49 PM by Sean Smith RSS

    Where not exists

      I created a table, Table1 with 1 field in it, called Field1.
      I then pull data from another source, table2 that contains the the same data  in field one of table1 but also has additional data.I want to add the data from table to, to table1, where it does not already exist in table1.  I am having trouble using the where not exist. My script looks like this,
      inner join (table1)
      Load
      field1 Resident table2 where not(Exists(%KEY_SDMATNR));
        • Where not exists
          Jean-Pierre Bakhache

          hello,

          i believe what you really need is to concatenate both sets of data.

           

          try this:

           

          table1:

          LOAD field1 FROM...

           

          table2:

          LOAD field1 as field2 FROM...

           

          Concatenate

           

          LOAD field1 as field2

          Resident table1

          Where not Exists(field2, field1);

           

          You'll get in table2 the full set of values.

          Hope this helps.

            • Where not exists

              I keep getting this error:    Table not found
              Concatenate
              LOAD
              %KEY_SDMATNR as %KEY_SDMATNR1
              Resident BillingDocs
              Where not Exists(%KEY_SDMATNR1, %KEY_SDMATNR);              My script is

              BillingDocs1:

              Load Distinct
              %KEY_SDMATNR
              Resident PASD;

              BillingDocs2:
              Load Distinct
              %KEY_SDMATNR as %KEY_SDMATNR1
              Resident KNOP;

              Concatenate
              LOAD
              %KEY_SDMATNR as %KEY_SDMATNR1
              Resident BillingDocs
              Where not Exists(%KEY_SDMATNR1, %KEY_SDMATNR);

               

               

               

              Thoughts?

                • Where not exists
                  Jean-Pierre Bakhache

                  In the last part, try to rename BillingDocs to BillingDocs1 as it is declared in the beginning of your script.

                    • Where not exists

                      Thanks I figured out the issue, but based on my previous code I now need to concatenate table1 with 4 fields and table 2 with 4 fields and I have yet to be sucessful.  The fields all called the same in each table, and I want to add the records from table 2 to table 1 that are not already in table 1.  I have tried a couple of things and I'm just missing something.  Can you send me the script example?  Thanks again, Sean

                        • Where not exists
                          Jean-Pierre Bakhache
                          Well in that case it seems that 4 fields constitute a unique record instead of 1. We can still proceed with the same logic as before, but this time using in addition a new field formed of those 4 fields as shown below:

                           

                          QUALIFY *;

                          table2:
                          LOAD Field1 & '|' & Field2 & '|' & Field3 & '|' & Field4 as KeyField,
                          Field1,
                          Field2,
                          Field3,
                          Field4
                          FROM ...
                          ;

                          table1:
                          LOAD Field1 & '|' & Field2 & '|' & Field3 & '|' & Field4 as KeyField,
                          Field1,
                          Field2,
                          Field3,
                          Field4
                          FROM ...
                          ;

                          UNQUALIFY *;

                          Concatenate
                          LOAD table2.KeyField as table1.KeyField,
                          table2.Field1 as table1.Field1,
                          table2.Field2 as table1.Field2,
                          table2.Field3 as table1.Field3,
                          table2.Field4 as table1.Field4
                          Resident table2
                          Where not Exists(table1.KeyField,table2.KeyField);

                          In the above code I used the Qualify/Unqualify in order to keep both of table2 and table1 (updated) in the tableviewer without creating synthetic keys since the fields in both tables have same names (you can also rename each field in one of the two tables instead of using the qualify/unqualify and then in the concatenate part you adjust the field names appropriately, it's up to you).
                          If you want instead to drop table2 in the end since table1 will contain all needed records then you can use the code below:


                          table2:

                          LOAD Field1 & '|' & Field2 & '|' & Field3 & '|' & Field4 as KeyField2,
                          Field1,
                          Field2,
                          Field3,
                          Field4
                          FROM ...
                          ;

                          table1:
                          LOAD Field1 & '|' & Field2 & '|' & Field3 & '|' & Field4 as KeyField,
                          Field1,
                          Field2,
                          Field3,
                          Field4
                          FROM ...
                          ;

                          Concatenate
                          LOAD KeyField2 as KeyField,
                          Field1,
                          Field2,
                          Field3,
                          Field4
                          Resident table2
                          Where not Exists(KeyField, KeyField2);

                          DROP Table table2;

                          Hope this helps you Sean.