Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ranibosch
Creator
Creator

Calculated field in script

Good day,

Please see my table below:

current table.JPG

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.

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

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;

View solution in original post

2 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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
Creator
Creator
Author

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