3 Replies Latest reply: Nov 29, 2013 9:34 AM by Alan Farrell RSS

    Calculation in Script

    ralph snijckers

      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
          Friedrich Hofmann

          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
            Ravikant Bellad

            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
              Alan Farrell

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