2 Replies Latest reply: Jul 31, 2012 11:25 PM by olfesio180 RSS

    about specification of Concatenate Load in scripts

      hi,

       

      please let me know that about subtitle.

      i'm wondering result to reload the following scripts.

       

      Item:

      LOAD

      Item_cd & '-' & Item_location as Primary Key,

      Item_qty;

      SQL

      select * from DB;

       

      CONCATINATE(Item) LOAD

      Item_cd & '-' & Item_location as Primary Key,

      Item_qty

      from file.qvd;

       

      which is this result update or insert as new data from qvd if both Primary Key are same?

      if result is insert then how can i update(delete & insert) to Item teble using Primary Key?

       

      please anyone tell me this matter...

       

      Regards,

        • Re: about specification of Concatenate Load in scripts
          Nicolas MARTIN

          Lets say you have 2 tables:

           

          TableSource:

          LOAD * INLINE [

          TableSource.PK, TableSource.Value

          1, 1

          2, 2

          3, 4

          4, 8

          5, 16

          ];

           

           

          TableUpdate:

          LOAD * INLINE [

          TableUpdate.PK, TableUpdate.Value

          4, UPDATED

          5, UPDATED

          6, 32

          7, 64

          ];

           

           

          If you load with a CONCATENATE, you will have inserted rows:

           

           

          Table:

          LOAD

          TableSource.PK AS PK,

          TableSource.Value AS Value

          RESIDENT TableSource;

           

          CONCATENATE (Table)

          LOAD

          TableUpdate.PK AS PK,

          TableUpdate.Value AS Value

          RESIDENT TableUpdate;

           

           

          2012-07-27_112342.png

           

           

           

          If you want that your table "TableUpdate" really update your data, I advise you to think upside down: you first load the "update" table, then you load the first table, but filtering on the PK that don't exist.

           

           

          Table:

          LOAD

          TableUpdate.PK AS PK,

          TableUpdate.Value AS Value

          RESIDENT TableUpdate;

           

           

          CONCATENATE (Table)

          LOAD

          TableSource.PK AS PK,

          TableSource.Value AS Value

          RESIDENT TableSource

          WHERE NOT EXISTS(PK, TableSource.PK);

           

           

          2012-07-27_112725.png

           

           

           

          If you have more than 2 sources, you will have to be smarter:

          - load data in a temp table, with a "date" field

          - left join the the last date in a "last_date" field with a "max()" function group by PK

          - load your data in a new table, filtering on "date = last_date"