topic Qlikview Document in QlikView

Qlikview Document

Wed, 27 Oct 2010 18:54:46 GMT

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?

Qlikview Document

Wed, 27 Oct 2010 19:12:40 GMT

Hi,

You can do this in load script. Just create a new field with this expression.

you can create a Straight Table and insert this expression "col1 / col2"

Qlikview Document

Wed, 27 Oct 2010 20:43:32 GMT

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

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.

Qlikview Document

Wed, 27 Oct 2010 20:43:34 GMT

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

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.

Qlikview Document

marcel_olmo — Thu, 28 Oct 2010 00:19:05 GMT

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

Qlikview Document

marcel_olmo — Thu, 28 Oct 2010 00:24:48 GMT

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!

Qlikview Document

Thu, 28 Oct 2010 14:12:25 GMT

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.

Qlikview Document

Thu, 28 Oct 2010 14:44:18 GMT

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.

Qlikview Document

Thu, 28 Oct 2010 20:30:16 GMT

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.