Joining data field in calculation from two tables

[#1]

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"???

BR

Kriss

[#2]

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

[#3]

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

BR

Kriss

[#4]

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

[#5]

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?

BR

Kriss

[swuehl]

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

[#7]

Hi

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"...

BR

Kriss

[swuehl]

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

[#9]

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.

BR

Kriss

[swuehl]

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