Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'dd like to have a calculated field into table PO_taak.
One of field for the calculation is from an other table (Vakgr). By reload I get the message "Field not found".
How can I get the field "TotalBudgetcost" (Taakbudget*[Vakgroep tarief]) in the table PO_taak?
Script:
Vakgr:
Directory;
LOAD ID_Vakgroep,
Ben_Vakgroep,
[Vakgroep tarief]
FROM
[..\..\..\SAM\SAM_Relaties_Brondata.xlsx]
(ooxml, embedded labels, table is Vakgroep);
PO_taak:
Directory;
LOAD ID_POPlan,
ID_Beurt,
ID_POTaakregel,
Ben_POTaakregel,
Taakbudget,
//Taakbudget*[Vakgroep tarief] as TotalBudgetcost,
ID_Vakgroep,
ID_Leverancier,
[Contract nr]
FROM
[..\..\..\SAM\SAM_Relaties_Brondata.xlsx]
(ooxml, embedded labels, table is POTaken);
Hi,
try this,
PO_taak:
LOAD ID_POPlan,
ID_Beurt,
ID_POTaakregel,
Ben_POTaakregel,
Taakbudget,
ID_Vakgroep,
ID_Leverancier,
[Contract nr]
FROM
[..\..\..\SAM\SAM_Relaties_Brondata.xlsx]
(ooxml, embedded labels, table is POTaken);
Left Join(PO_taak):
LOAD ID_Vakgroep,
Ben_Vakgroep,
[Vakgroep tarief]
FROM
[..\..\..\SAM\SAM_Relaties_Brondata.xlsx]
(ooxml, embedded labels, table is Vakgroep);
PO_taakFinal:
LOAD ID_POPlan,
ID_Beurt,
ID_POTaakregel,
Ben_POTaakregel,
Taakbudget,
Taakbudget*[Vakgroep tarief] as TotalBudgetcost,
ID_Vakgroep,
ID_Leverancier,
[Contract nr]
Resident PO_taak
Drop Table PO_taak;
Hope it helps
Regards,
Ravikant
Hi ralph,
in the script, you can use the PEEK() function to reference fields in a previously loaded table. It takes one or several parameters - take care about the upper_quotes.
HTH
Best regards,
DataNibbler
Hi,
try this,
PO_taak:
LOAD ID_POPlan,
ID_Beurt,
ID_POTaakregel,
Ben_POTaakregel,
Taakbudget,
ID_Vakgroep,
ID_Leverancier,
[Contract nr]
FROM
[..\..\..\SAM\SAM_Relaties_Brondata.xlsx]
(ooxml, embedded labels, table is POTaken);
Left Join(PO_taak):
LOAD ID_Vakgroep,
Ben_Vakgroep,
[Vakgroep tarief]
FROM
[..\..\..\SAM\SAM_Relaties_Brondata.xlsx]
(ooxml, embedded labels, table is Vakgroep);
PO_taakFinal:
LOAD ID_POPlan,
ID_Beurt,
ID_POTaakregel,
Ben_POTaakregel,
Taakbudget,
Taakbudget*[Vakgroep tarief] as TotalBudgetcost,
ID_Vakgroep,
ID_Leverancier,
[Contract nr]
Resident PO_taak
Drop Table PO_taak;
Hope it helps
Regards,
Ravikant
Hi,
Have you tried using ApplyMap.
You have a common field for both tables called ID_Vakgroep
create a mapping table like
Map_Vakgr:
Mapping LOAD ID_Vakgroep,
[Vakgroep tarief]
FROM
[..\..\..\SAM\SAM_Relaties_Brondata.xlsx]
(ooxml, embedded labels, table is Vakgroep);
And then in the next table use the applymap to add the [Vakgroep tarief]
PO_taak:
Directory;
LOAD ID_POPlan,
ID_Beurt,
ID_POTaakregel,
Ben_POTaakregel,
Taakbudget,
ID_Vakgroep,
APPLYMAP('Map_Vakgr',ID_Vakgroep, null()) AS [Vakgroep tarief],
ID_Vakgroep*[Vakgroep tarief] AS TotalBudgetcost
ID_Leverancier,
[Contract nr]
FROM
[..\..\..\SAM\SAM_Relaties_Brondata.xlsx]
(ooxml, embedded labels, table is POTaken);