Calculation in Script

[#1]

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);

[datanibbler]

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

[#3 · Accepted solution]

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

[rustyfishbones]

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);