topic Re: Linking the tables in QlikView

Linking the tables

Tue, 13 Sep 2011 11:13:44 GMT

Hi Guys,

I have a two tables called sales and purchase of the company and those two tables has [company name] and [item id] in common.

How can I map those two tables and create some charts..?

Can anyone help me in this..?

Thanks

Rajan.

Linking the tables

its_anandrjs — Tue, 13 Sep 2011 11:39:35 GMT

Hi,

As my understanding i suggest link this two table with [item id] by this you are able to map this tables and able to make charts.

Regards

Anand

Linking the tables

martinpohl — Tue, 13 Sep 2011 11:42:56 GMT

Hi Rajan,

the native QlikView priniciple is to join the tables by the two fields company name and item id by itself.

Then you will get a synthetic key, which is not fine in QlikView.

So you have to edit the loading script to

[company code]&[item id] as ccii

as a new key field in both tables.

For a singel selection hold the company code and the item id in one table (prefer the table that includes all datas).

So you will have one key field between both tables and can select all datas.

Regards

Re: Linking the tables

its_anandrjs — Tue, 13 Sep 2011 12:06:26 GMT

I think you need to load some thing like this

Qualify *;

Unqualify CID;

Sales:

LOAD

[Company Name],

[Item Id],

[Company Name]&'-'&[Item Id] as CID

FROM

..\Data.xlsx

(ooxml, embedded labels, table is Sales);

Purchase:

LOAD

[Company Name],

[Item Id],

[Company Name]&'-'&[Item Id] as CID,

Value

FROM

..\Data.xlsx

(ooxml, embedded labels, table is Purchase);

See the attached sample file.

HTH

Regards

Anand

Re: Linking the tables

its_anandrjs — Tue, 13 Sep 2011 12:10:39 GMT

Hi,

Another way you join this two table and make one table like

Sales:

LOAD

[Company Name],

[Item Id]

FROM

Data

Purchase:

Join(Sales)

LOAD

[Company Name],

[Item Id],

Value

FROM

Data

HTH

Regards

Anand

Re: Linking the tables

Tue, 13 Sep 2011 12:38:33 GMT

I guess it depends on what you want to graph. The suggestions above will only link the companies that have both sold someting and purchased something and that is probably not what you want.

One option is to do a concatenate load.

FactTable:

Load

'Sales' as RecordType, //this field could be used a dimension on your graph

[Company Name],

[Item Id],

SalesDate as TransactionDate, //Assuming you have a date there. Creating a common date field

QTY as QtySold

FROM

SalesFacts

Concatenate Load

'Purchase' as RecordType,

[Company Name],

[Item Id],

PurchaseDate as TransactionDate,

Value as QtyPurchased,

FROM

PurchaseFacts

The resulting table will have all the companies and id's listed.

Final Structure:

RecordType, Company Name, Item Id, TransactionDate, QtySold,QtyPurchased