Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to add calculated fields to a table box. I have two colums with data that I want to create a third column with by diving col1 inot col2. Also i'd like to take each row calculated and add them together for input into a list box as total of that deried column. is that possible in qlikwiew. If so how do I do it?
Hi,
You can do this in load script. Just create a new field with this expression.
OR
you can create a Straight Table and insert this expression "col1 / col2"
This is my current script.
"
SQL
Select product_group_id as idConp, product_group_descfrom
COMPUWASH.dbo.product_group;
SQL
SELECT
product_group_id
as idConp ,item_id AS idCon, inv_mast_uid as
idcons
from
COMPUWASH.dbo.p21_view_inv_loc where delete_flag='n';
;
SQL
SELECT
qty_per_assembly
as
ComponentQty,
assembly_item_id
AS idCon, item_id as
SubComponentID,
item_desc
AS Component_Desc, unit_of_measure as
[UOM Per Assembly]
FROM
COMPUWASH.dbo.p21_view_assembly_lines;
SQL
SELECT
item_id,
item_desc,purchase_pricing_unit,purchase_pricing_unit_size,
item_id
AS idCon, price1, price1/sales_pricing_unit_size as
calcualted_field
FROM
COMPUWASH.dbo.p21_view_inv_mast;
SQL
select inv_mast_uid as idcons,cost as
CostMain,inv_mast_uid
from
COMPUWASH.dbo.inventory_supplier where delete_flag = 'n';
SQL
Select distinct item_id AS SubComponentID ,SupplierCost,CostPerItem
from
COMPUWASH.dbo.compuwash_components_view
".
How do I do te same thing in load scripts and also do calculations combining or use fields from 2 to 3 different tables.
This is my current script.
"
SQL
Select product_group_id as idConp, product_group_descfrom
COMPUWASH.dbo.product_group;
SQL
SELECT
product_group_id
as idConp ,item_id AS idCon, inv_mast_uid as
idcons
from
COMPUWASH.dbo.p21_view_inv_loc where delete_flag='n';
;
SQL
SELECT
qty_per_assembly
as
ComponentQty,
assembly_item_id
AS idCon, item_id as
SubComponentID,
item_desc
AS Component_Desc, unit_of_measure as
[UOM Per Assembly]
FROM
COMPUWASH.dbo.p21_view_assembly_lines;
SQL
SELECT
item_id,
item_desc,purchase_pricing_unit,purchase_pricing_unit_size,
item_id
AS idCon, price1, price1/sales_pricing_unit_size as
calcualted_field
FROM
COMPUWASH.dbo.p21_view_inv_mast;
SQL
select inv_mast_uid as idcons,cost as
CostMain,inv_mast_uid
from
COMPUWASH.dbo.inventory_supplier where delete_flag = 'n';
SQL
Select distinct item_id AS SubComponentID ,SupplierCost,CostPerItem
from
COMPUWASH.dbo.compuwash_components_view
".
How do I do te same thing in load scripts and also do calculations combining or use fields from 2 to 3 different tables.
Sorry, if you don't upload a QV file with the information, it's hard for us to help you if you just put the row code.
You can make a lot of stuff in the loading script with Qlikview. There is a bunch of help in the "help button" of Qlikview that explains you the functions and common examples.
If you just want to add a calculated value (like a division), I'm gonna show you the concept, and then you apply this to your case. (Including a controlling error if the field2 is 0 ).
YourTableName :
Load
field1,
field2,
if (field2 <> 0, field1 / field2, 'error or exception') as field3 ;
select
field1,
field2
from yourLoadedTable;
Hope that helps!!
If you want to concat the tables, I recommend you the CONCATENATE option, and if you want to do additional modifications of your loaded tables, I recommend you to use the RESIDENT option.
It is very well documentated in the "help" tab, with their own "easy to learn" examples.
Hope it helps!!
See you around!
I've uploaded my document and would appreciate any help on the actual document. I want to do the operation purchased uom cost/purchased uom; the actual fields are purchase_pricing_unit_size/costperitem. I then want to add each generated column to create or populate a text box object with that value named TotalCostOfAssembly. If possible please help thanks.
Hi,
you need to use JOIN's to find your result, and just not using fields to to link tables.
I create a script with your model, and convert to a single table (RESULT).
In this case you have all fields and you can to create your expression in load script.
Everything looks great and works as expected. I want to know how I can summarize the EXPRESSION_RESULT into a new listbox. Thanks for all the help so far.