topic Joining data field in calculation from two tables in QlikView

Joining data field in calculation from two tables

Tue, 26 Jun 2012 15:13:21 GMT

Hi,

I am a beginner at using/developing in QV so excuse my rudimentary questions..:)

1.) I would like to use a data field from one table (loaded into script) as a value in another table. The two tables are joined by primary key.

Example:

When trying to use the data field "OrderQty" from table "OrderDetail" - in - table "Product" (which doesn´t contain a data field form QrderQty) I get an error message.. when loading data.

How do I use the "join" function to be able to calculate new values in the table "Product" with the help of data field "OrderQty"???

Kriss

Re: Joining data field in calculation from two tables

Tue, 26 Jun 2012 15:22:12 GMT

Hi,

Instead of Joining, you can link these 2 tables with the primary key,

The advantage of qlikview is AQL (Associative Query Logic), Which give more felxibility on data association.

AQL is a query language based on a data base management system which uses Codd's relational model of data. It has been designed mainly to be used by the nonspecialist in data processing for interactive problem solving, application building, and simulation. Ease of use is achieved by providing an interface which allows the use of default options, synonyms, and definitions of attributes, inference, and the possibility of interactive completion of the query (i.e., menu). AQL combines the capabilities of the relational model of data with the powerful computational facilities and control structure of the host programming language (i.e., APL). A prototype version of AQL, which has been implemented, is reviewed.

Regards,

Prasath

Re: Joining data field in calculation from two tables

Tue, 26 Jun 2012 15:49:56 GMT

Hi,

thanks for very rapid reply!

- How do I create(use) the value OrderQty in the table "Product"

The calculation I would like to acheive is to use values for OrderQty together with StandardCost to create totCost... (OrderQty * StandardCost)... but I do not know how to load / join / connect the two data fields from the two separate tables

- and they are joined by a p key "ProductID"

-- Please show how to make the calculation in one of the tables

.......

SalesOrder:

LOAD SalesOrderID,

ProductID,

UnitPrice,

OrderQty,

OrderQty * UnitPrice as Sales1

FROM

[..\\QVD\SalesOrder.qvd]

(qvd);

Product:

LOAD ProductID,

Name,

ProductNumber,

StandardCost,

ListPrice

FROM

[..\\QVD\Product.qvd]

(qvd);

Kriss

Re: Joining data field in calculation from two tables

Tue, 26 Jun 2012 15:54:55 GMT

Hi,

Yes they are joined using primary key ProductID.

press CTRL+T, which will show the table structure how the tables are linked in the application.

Step - 1.

Add Straight Table -> in dimensions put ProductID.

Step - 2

Go to expressions -> sum(Orderqty) * sum(standardcost)

name this expression as totalcost

Regards,

Prasath

Re: Joining data field in calculation from two tables

Tue, 26 Jun 2012 16:05:24 GMT

Hi Prasath!

Thank you for your attention and assistance.

- so you mean that I cannot create this new value " totCost" in the code in the script editor ?

Do I have to use a straight table / object... I will want to use this new value "totCost" several times again and would like for it to be in the script

- should I perhaps create a new table (in the script) with the two values in it?

Kriss

Re: Joining data field in calculation from two tables

swuehl — Tue, 26 Jun 2012 16:21:57 GMT

You can join your tables or you could f.e. use a lookup:

Product:

LOAD ProductID,

Name,

ProductNumber,

StandardCost,

ListPrice

FROM

[..\\QVD\Product.qvd]

(qvd);

SalesOrder:

LOAD SalesOrderID,

ProductID,

UnitPrice,

OrderQty,

OrderQty * UnitPrice as Sales1,

OrderQty * lookup('StandardCost','ProductID',ProductID,'Product') as TotalCost

FROM

[..\\QVD\SalesOrder.qvd]

(qvd);

Re: Joining data field in calculation from two tables

Tue, 26 Jun 2012 16:35:43 GMT

thank you very much for assisting.

The code doesn´t seem to work properly..

- there is no calculation done for the value.. "TotalCost"

Can there be some error in syntax..

OrderQty * lookup('StandardCost','ProductID',ProductID,'Product') as TotalCost

the table name is "Product" .. and the primary key for both tables is "ProductID"...

Kriss

Re: Joining data field in calculation from two tables

swuehl — Tue, 26 Jun 2012 16:38:30 GMT

There can always be an error, I haven't tested the code. Please note that the Product table needs to be loaded already (that's why I changed the order of loads), when using the lookup ...

Re: Joining data field in calculation from two tables

Tue, 26 Jun 2012 18:19:41 GMT

Hi Swuehl,

it seems to be working after a couple of reloads... (?) !!

Thank you very much for your assistance.

Which is the correct syntax formula for lookup - lookup('fieldname' , 'pkey_?' , 'pkey ?' , ) ?

Thanks in advance.

Kriss

Re: Joining data field in calculation from two tables

swuehl — Wed, 27 Jun 2012 08:36:36 GMT

From the Help:

lookup(fieldname, matchfieldname, matchfieldvalue [, tablename])

Returns the value of fieldname corresponding to the first occurrence of the value matchfieldvalue in the field matchfieldname.

Fieldname, matchfieldname and tablename must be given as strings (e.g. quoted literals).

The search order is load order unless the table is the result of complex operations such as joins, in which case the order is not well defined.

Both fieldname and matchfieldname must be fields in the same table, specified by tablename. If tablename is omitted the current table is assumed.

If no match is found, null is returned.

Example:

lookup('Price', 'ProductID', InvoicedProd, 'pricelist')

In your setting you need to use 'ProductID' resp. ProductID for matchfieldname and matchfieldvalue, fieldname is 'StandardCost' and your tablename is 'Product'.

Re: Joining data field in calculation from two tables

Wed, 27 Jun 2012 10:53:56 GMT

Hi Swuehl,

thank you very much for this syntax reference!

The code with lookup() works and I am able to calculate the value.

Thanks again.

- Would you mind assisting in one more question on Concatenation?

I am having trouble creating a new table where I can see the datafields from a concatenation of two tables..

I try naming it according to text in "help" text but not sure that I am doing it right.

- should the original tables (Sales) (quota) already be loaded into QV, logical table ?

-- then do I use "select" of fields from (Sales) into "new table" with concatenate of fields from (quota) table...

How do I write this code - to get the new table visible in Script editor.. is it possible?

Kristina

Re: Joining data field in calculation from two tables

swuehl — Wed, 27 Jun 2012 11:24:00 GMT

Kristina,

QV will automatically concatenate a table to another if these two tables having exactely the same amount and named fields.

Tab1:

LOAD

A, B

from Table1.xls;

LOAD

A, B

from Table2.xls;

resulting in one table Tab1.

You can also force concatenation

Tab1:

LOAD

A, B

from Table1.xls;

CONCATENATE (Tab1) LOAD

from Table2.xls;

also resulting in one table Tab1, Field B only set with values for records loaded from Table1.xls.

This is mostly it. Instead of loading from Excel files, you can of course reference resident tables.

Hope this helps,

Stefan