topic Re: Calculation in Script in QlikView

Calculation in Script

Fri, 29 Nov 2013 13:50:05 GMT

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

Re: Calculation in Script

datanibbler — Fri, 29 Nov 2013 14:00:41 GMT

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

Re: Calculation in Script

Fri, 29 Nov 2013 14:16:19 GMT

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

Re: Calculation in Script

rustyfishbones — Fri, 29 Nov 2013 14:34:03 GMT

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