9 Replies Latest reply: Mar 21, 2013 2:13 PM by Ronald Salas RSS

    Updating value of a key field

      Hi,

       

      I have a table that has the following fields, as an example: ContractID, EndDate, Amount1, Amount2. The keys in my table is ContractID and EndDate. I need to update the value of the EndDate field and I've been using a Lookup table to massively replace the values, and I haven't been successful as of to date. I Read the Table, use a Transform operator to use the lookup table and Write on the same Table with the mode changed to 'Update' (and now I having doubts that I did that correctly)

      Dataflow.jpg

       

      Help please..

       

      Thanks!

        • Re: Updating value of a key field
          Hugo Sheng

          Are you trying to update your lookup table values?

            • Re: Updating value of a key field

              Hi Hugo,

               

              No, I’m updating/replacing the values in a table using a lookup table. I have a table that looks something like this:

               

              ContractID          EndDate               Amount1             Amount2

              1                              2009/10/30         100                         203

              1                              2009/11/30         320                         230

              2                              2009/10/30         322                         231

              2                              2009/11/30         231                         234

               

              And I need to update the EndDate column to a more recent date, like this:

               

              ContractID          EndDate               Amount1             Amount2

              1                              2012/10/29         100                         203

              1                              2012/11/31         320                         230

              2                              2012/10/29         322                         231

              2                              2012/11/31         231                         234

               

              Since each period is discrete, I am using a lookup table to change the old EndDate to the appropriate new EndDate.

               

              Thanks!

            • Re: Updating value of a key field
              Nagaian Krishnamoorthy

              A sample application is attached. Though the data and transformation used are slightly different, hope this helps you make progress in your development.

              • Re: Updating value of a key field

                I think you are very close, but as Hugo mentioned, do you really have duplicate ContractID's and is that value the primary key in your database table?  If so, you may be updating the target row multiple times and there is no guaraantee that the updates will be made in chronological order.

                 

                Anyway, the Read Table reads the current contents of the table.  In the Transform, use a Lookup Expression Rule to retrieve all records from your lookup table that have the same EndDate.  This may return multiple rows so your lookup table's key based on EndDate needs to be non-unique.  In the Transform Rule Editor, map the EndDate value retrieved from the lookup table to the EndDate attribute in the output.  This replaces the old EndDate value with the new value retrieved from the lookup table.  Finally, the Write Table operator performs the update using, I assume, the ContractID as the key.  Note that the composite type in the schema used by the Write Table operator only needs the primary key column(s) and EndDate; it's not necessary to include the attributes corresponding to the table columns that are not altered.

                 

                Is this what you intended to do?

                  • Re: Updating value of a key field

                    Yeah, now that I had time to think about it, I may be making it too complex for my own good. I may need to simplify the approach I take.

                     

                    Thanks John for the additional info on keys on the use of Lookup tables. I am sure that will be useful once I resolve what should be the keys in the table I am trying to transform.