topic Re: New Field Creation - Join and Synthetic keys in the Data Load Editor in App Development

New Field Creation - Join and Synthetic keys in the Data Load Editor

abonnery — Tue, 08 Jun 2021 07:55:06 GMT

Hello,

I am having trouble creating a new field in the data load editor. The script is below the message.

Here is the data model (capture 1).

I want to avoid any synthetic key to appear when I create this new 'IA/VIA' field but I need to load within the table 2 dimensions that are keys from other tables.

I tried dropping 2 of the dimensions (I still need one key) but it still didn't work ... (see capture 2) The IA/VIA field doesn't appear and the synthetic keys don't go...

I don't know if I did properly the join or if I need to add a group by clause (I don't understand how it works).

The 2 dimensions that are keys from other table are "Project Name", "Responsible Name" and "IA GBU" (and probably customer name but I forgot it in the script).

I don't think the issue is coming from the way the new field itself is defined but more of the structure.

Thank you very much for your help !

SCRIPT:

IA_VIA_Definition:

LOAD "Project Name","Customer Name","Project Program","Responsible Name" resident BO_ProjectsCost;

Join(IA_VIA_Definition)

LOAD "Responsible Name","IA GBU" resident Responsible_Data;

Join(IA_VIA_Definition)
Load if("IA GBU"=A or "IA GBU"=B or "IA GBU"=C,'IA',
if(("IA GBU"=D or "IA GBU"=E) and Left("Customer Name",4)='SITE' and "Customer Name"<>'SITE - xxx'
and "Project Program"<>'Hello', 'VIA', 'OTHER')) AS "IA/VIA" ;

Drop field "Responsible Name","IA GBU" from BO_ProjectsCost;

Re: New Field Creation - Join and Synthetic keys in the Data Load Editor

Sammy_AK — Tue, 08 Jun 2021 18:36:09 GMT

synthetic keys are created on data association, which is that if more than 1 table has more than 1 common column names then synthetic keys are created. for e.g. Table a has ID, Color, product, amount and table b has ID, color, country, value. here a synthetic key is created between ID and color as they are common keys between 2 tables. synthetic keys are not bad but as long as they give correct output they are fine to be there.

in your case, i understand that there are more than 1 common column between more than 1 table so synthetic keys are created. you can rename the column names apart from the columns you want to associate data. similarly in the above example, if i want to get rid of synthetic keys i would rename /delete color column either from table a or b. i would rename the column or give a alias for column color to prd_color (i.e. color as prd_color) and this should solve the problem.

try this updated Script:

SCRIPT:

IA_VIA_Definition:

LOAD "Project Name","Customer Name","Project Program","Responsible Name" resident BO_ProjectsCost;

Join(IA_VIA_Definition)

LOAD "Responsible Name","IA GBU" resident Responsible_Data;

IA_VIA:
Load if("IA GBU"=A or "IA GBU"=B or "IA GBU"=C,'IA',
if(("IA GBU"=D or "IA GBU"=E) and Left("Customer Name",4)='SITE' and "Customer Name"<>'SITE - xxx'
and "Project Program"<>'Hello', 'VIA', 'OTHER')) AS "IA/VIA",

"Project Name"

resident IA_VIA_Definition;

Drop table IA_VIA_Definition;

i am assuming project name is the key to link bo_project_cost

Re: New Field Creation - Join and Synthetic keys in the Data Load Editor

abonnery — Thu, 17 Jun 2021 12:30:12 GMT

Hello,

thank you for you answer. It still doesn't work. The data loading never ends.

Maybe because in that second IA/VIA table, the conditions to create the IA/VIA field cannot be verified because the "customer name", "project program" and "IA GBU" are not loaded in this same table but in the other one ...

Re: New Field Creation - Join and Synthetic keys in the Data Load Editor

Sammy_AK — Tue, 22 Jun 2021 10:09:59 GMT

do you have a sample qvf file to share ?