Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Linking the tables

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.

5 Replies

Linking the tables

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

martinpohl
Valued Contributor II

Linking the tables

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

Hi

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

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

Not applicable

Re: Linking the tables

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

Community Browser