topic Re: Different tables with the same fields in QlikView

Different tables with the same fields

sprqlikview — Thu, 12 Mar 2015 12:38:46 GMT

I have the following situation

I have the following tables

1. Orders

2. Production

3. Distribution to stores

4. sales information

In all the tables I have Article numbers, Dates, cost centre and obviously the corresponding figures.

I have linked the different tables through the Article number and therefore if I filter for an article number it works fine. If I however want to filter for cost centre, I need to take 4 different filters, one for each table and there I would e.g. Filter for

orders.costcentre = 815

production.costcentre = 815

distribution.costcentre = 815

sales.costcentre = 815

This allows me to compare on a cost centre level.

Is there a way to link the costcentres of the different tables and have only one filter/listtable?

Another problem I have: I have a different date range for production than for distribution and sales

E.g.

orders.date between 20140801 and 20141231

production.date between 20141001 and 20141231

distribution.date between 20141101 and 20150131

sales.date between 20150101 and 20150228

Re: Different tables with the same fields

rubenmarin — Thu, 12 Mar 2015 12:51:37 GMT

Hi Matias, I usually solve thing like this concatenating all tables in an unique fact table:

FactTable:

LOAD *, orders.date as Date, 'O' as DocType Resident Orders;

Concatenate *, production.date as Date, 'P' as DocType Resident Production;

Concatenate *, Distribution.date as Date 'D' as DocType Resident Distribution;

Concatenate *, sales.date as Date, 'S' as DocType Resident Sales;

DROP Tables Orders, Production, Distribution, Sales;

This way selectiong a value of Date, will affect all the tables. It's an example, probably you'll need to do the same twith Product, CostCentre, etc... so there is the same field for all the tables.

Using a field like DocType can be usefull to filter data.

Re: Different tables with the same fields

hic — Thu, 12 Mar 2015 12:53:44 GMT

It seems to me as if costcentre and articlenumber both are keys, whereas the four dates are different dates. If so, you should link on both if costcentre and articlenumber. You will get a synthetic key, but this is not a problem.

Concerning the four dates - take a look at Canonical Date. You could make a date bridge with costcentre, articlenumber, Date and DateType

HIC

Re: Different tables with the same fields

puttemans — Thu, 12 Mar 2015 12:57:09 GMT

Or you could create 1 key with all mutual variable in it in all tables.

Like : articlenumber&costcentre&... as %Key

Re: Different tables with the same fields

buzzy996 — Thu, 12 Mar 2015 13:03:02 GMT

hopes give some idea,

Tab:

Load * Inline [

Dim,Exp1,Exp2,Exp3

a,100,200,10

b,200,300,20

c,400,500,30

d,600,300,40

e,300,800,50

];

Qualify*;

Tab2:

Load * Inline [

Dim,Exp1,Exp2,Exp3

a,100,200,10

b,200,300,20

c,400,500,30

d,600,300,40

e,300,800,50

];

Key:

Load *

Resident Tab;

Concatenate Load *

Resident Tab2;

Drop Tables Tab,Tab2;

Re: Different tables with the same fields

sprqlikview — Thu, 12 Mar 2015 13:21:41 GMT

Don't understand I'm afraid. Would I need some kind of Unqualify? like this I don't have a key

Re: Different tables with the same fields

trdandamudi — Thu, 12 Mar 2015 13:27:49 GMT

Use the Link Table concept as below:

Orders:

Load

Article_Number &'|'& Date &'|'& Cost_Center as KeyField,

Article_Number,

Date,

Cost_Center,

Orders_Field1,

Orders_Field2

From <Excel File 1>;

Production:

Load

Article_Number &'|'& Date &'|'& Cost_Center as KeyField,

Article_Number,

Date,

Cost_Center,

Production_Field1,

Production_Field2

From <Excel File 2>;

Distribution:

Load

Article_Number &'|'& Date &'|'& Cost_Center as KeyField,

Article_Number,

Date,

Cost_Center,

Distribution_Field1,

Distribution_Field2

From <Excel File 3>;

Sales:

Load

Article_Number &'|'& Date &'|'& Cost_Center as KeyField,

Article_Number,

Date,

Cost_Center,

Sales_Field1,

Sales_Field2

From <Excel File 4>;

LinkTable:

Load

DISTINCT

KeyField,

Article_Number,

Date,

Cost_Center

Resident Orders;

Concatenate(LinkTable)

Load

DISTINCT

KeyField,

Article_Number,

Date,

Cost_Center

Resident Production;

Concatenate(LinkTable)

Load

DISTINCT

KeyField,

Article_Number,

Date,

Cost_Center

Resident Distribution;

Concatenate(LinkTable)

Load

DISTINCT

KeyField,

Article_Number,

Date,

Cost_Center

Resident Sales;

// Drop the fields after keyField is created.

Drop Fields

Article_Number,

Date,

Cost_Center

From Orders;

Drop Fields

Article_Number,

Date,

Cost_Center

From Production;

Drop Fields

Article_Number,

Date,

Cost_Center

From Distribution;

Drop Fields

Article_Number,

Date,

Cost_Center

From Sales;

Re: Different tables with the same fields

buzzy996 — Thu, 12 Mar 2015 13:28:23 GMT

Is up to you.

I think u mentioned in ur first post "I have linked the different tables through the Article number ",I thought u can use this as a key, and play around.

Re: Different tables with the same fields

Thu, 12 Mar 2015 13:32:12 GMT

Hello,

In this case there are two things you can do.

1. The first think you can try to do is concatenate the values of the tables and create a new column, so you can identify which table each row refers.

It is going to be something like:

2. Another way to solve this problem is creating a link table, to create a link table you’ll have to create a new table cacatenating the costcentre of all tables and using the function autonumber() you will genarete a key of concatenation of the costcentre fields.

In this link they explain very well how to create a link table.

Link: Table in QlikView | Learn QlikView