Replacing partner ID with the parent partner ID if available

Sun, 10 Sep 2017 09:03:45 GMT

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_delta — Mon, 11 Sep 2017 15:02:54 GMT

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

topic Replacing partner ID with the parent partner ID if available in App Development

Replacing partner ID with the parent partner ID if available

Re: Replacing partner ID with the parent partner ID if available