Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Calculation in Script

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


1 Solution

Accepted Solutions
Not applicable

Re: Calculation in Script

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

3 Replies
datanibbler
Esteemed Contributor

Re: Calculation in Script

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

Not applicable

Re: Calculation in Script

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
Honored Contributor II

Re: Calculation in Script

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

Community Browser