Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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 ...
do you have a sample qvf file to share ?