topic Calculated field in script in QlikView

Calculated field in script

ranibosch — Tue, 24 Oct 2017 11:32:20 GMT

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.

Re: Calculated field in script

felipedl — Tue, 24 Oct 2017 11:38:10 GMT

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;

Re: Calculated field in script

ranibosch — Tue, 24 Oct 2017 12:46:29 GMT

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