Calculated field in script

[ranibosch]

Good day,

Please see my table below:

I would like to create a new field in my ValueEntries table called Invoiced Units.

This will be a calculated field:

by dividing the Invoiced quantity in the ValueEntries table for each transaction by the Packsize UOM field located in the DefaultDimension table.

What should the formula in my script be to achieve this?

*Note, my ValueEntries table contains data of each transaction related to sales.

[felipedl · Accepted solution]

Hi Rani,

First, you need to join the two tables

left join (ValueEntries)

Load

     [Item No],[PackSize UOM]

Resident DefaultDimension;

This will get the Packsize field into the ValueEntries table.

nex, do the calculation

NewValueEntries:

Load

[Item No],

[Document No],

[Value Entries Sales Amount],

[Value Entries Cost Amount]

[Invoiced Quantity],

[Invoiced Quantity]/[PackSize UOM] as NewField

Resident ValueEntries;

[ranibosch]

Thanks Felip.

How does this code change if I already have a join for my DefaultDimension table?

Extract of script below:

//********************************DEFAULT DIMENSION ON ITEMS****************************************************

DefaultDimension:

Load

     "No_" as "Item No",

    "Dimension Code" as "Trending Code",

    "Dimension Value Code" as "Trending Value";

SQL SELECT

     "No_",

    "Dimension Code",

    "Dimension Value Code"

FROM "Default Dimension"

where "Dimension Code" in ('CHANNEL', 'MFGTYPE', 'PACKSIZE', 'SERVICE');

//**************************JOIN PACKSIZE VALUE TO DEFAULT DIMENSION TABLE***********************

Left Join (DefaultDimension)

Load

     Code as "Trending Value",

     Name as "Packsize UOM";

SQL SELECT

          "Dimension Code",

         Code,

         Name

FROM "Dimension Value"

where "Dimension Code" = 'PACKSIZE';

//*************************VALUE ENTRIES**************************************

ValueEntries:

Load

     "Document No_" as "Document No",

     "Item No_" as "Item No",

     "Sales Amount (Actual)" as "Value Entries Sales Amount",

     "Cost Amount (Actual)" AS "Value Entries Cost Amount",

     "Invoiced Quantity";

SQL SELECT

    "Document No_",

    "Item No_",

    "Item Ledger Entry Type",

    "Sales Amount (Actual)",

    "Cost Amount (Actual)",

    "Invoiced Quantity"

FROM "Value Entry"

Where "Item Ledger Entry Type" = '1';