Qlikview Document

[#1]

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?

[#2]

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"

[#3]

This is my current script.

"

SQL

Select product_group_id as idConp, product_group_desc

from

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.

[#4]

This is my current script.

"

SQL

Select product_group_id as idConp, product_group_desc

from

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.

[marcel_olmo]

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!!

[marcel_olmo]

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!

[#7]

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.

[#8]

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.

[#9]

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.