1 Reply Latest reply: Sep 11, 2017 11:02 AM by Andrei-Dragos Delta RSS

    Replacing partner ID with the parent partner ID if available

    Seppe De Roeck

      Hi all,

       

      I have a Partners table and a Facts table, linked on the field %PartnerKey.

      Now some partners have a parent record.  In this case I want to replace the %PartnerKey in the Facts table with the key of the parent.

      I have some code to do this, but keep bouncing into the fact that the Staging table is not properly loaded.  I don't see where the mistake is.

       

      Below is the code.  Can someone direct me into what I am doing wrong ?

       

      tx,

      Seppe

       

      FactsStageXYZ:

      Load

           *,

          1 as temptest     //need to add an additional field, otherwise table is not loaded ??

      resident Facts;

       

      Left join(Facts)

      Load

           %PartnerKey,

          if(len(PartnerParentId)>0,  PartnerParentId, %PartnerKey) as NewPartnerKey

      Resident Partners;

       

      DROP TABLE Facts;

      Drop field %PartnerKey from FactsStageXYZ;

      Rename field NewPartnerKey to %PartnerKey;     //Error that %PartnerKey already exists

      RENAME TABLE FactsStageXYZ to Facts;

        • Re: Replacing partner ID with the parent partner ID if available
          Andrei-Dragos Delta

          Hello,

           

          From the beginning you load all the fields from the Facts to FactsStageXYZ and a big synthetic key is resulting so that;s why you need to put the "temptest" field. You could Qualify the fields you want to take from the Facts table to your new FactsStageXYZ.


          if  NewPartnerKey is the new key you want to use then before renaming it you could drop the old %PartnerKey or raname it so you don;t get the Error. Also i think you can write like this the left join:

          "Left join(Facts)

          Load 

              if(len(PartnerParentId)>0,  PartnerParentId, %PartnerKey) as %PartnerKey

          Resident Partners;"


          I really don;t understand why you are load the facts again in in the FactsStageXYZ...


          Hope it helps.

          Andrei