Replacing partner ID with the parent partner ID if available

[#1]

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;

[andrei_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