topic Re: Facttable in QlikView

Facttable

Wed, 09 Sep 2015 10:30:07 GMT

Hello experts im actually wondering about Facttable, i have a task from school to do, it is about circular references, synthetic keys and make a Facttable, i wonder why i should have a Facttable, or do i really need it?

Re: Facttable

sunilkumarqv — Wed, 09 Sep 2015 10:36:31 GMT

Fact table where facts lies in i.e measures

yes off course we need it to analyse the data where dimension stores the data

Dimensions and Measures

Re: Facttable

gautik92 — Wed, 09 Sep 2015 10:40:08 GMT

you have to use joins ,applymap() to remove synthetic keys and circular references the data table with synthetic tables or circula referennces will be inconsistent

first have a look at this

What is Synthetic Keys.pdf

and have a look at attached pdf

Re: Facttable

krishna20 — Wed, 09 Sep 2015 10:43:39 GMT

Hi,

Some of Clear discussion over here.It may hepls

What is the difference between a fact table and a dimension table ?

Multiple fact tables, same dimensions

Dimension,Fact and Link Table

Re: Facttable

gautik92 — Wed, 09 Sep 2015 11:11:03 GMT

This is how ur Datamodel should look like

Re: Facttable

maxgro — Wed, 09 Sep 2015 11:22:15 GMT

It seems to me you don't have any circular reference; your model should work as is; try to add some charts and see what it happens, problems, etc....

I think you can have some problems if you want to compare budget to sales (by year).

Re: Facttable

Wed, 09 Sep 2015 11:52:18 GMT

Thank you, how did you do the join function? i really have to understand the logic behind this

Re: Facttable

Wed, 09 Sep 2015 11:55:32 GMT

The synthetic key problem is now gone because i just did like that.

Itemnumber & line number was an common column

Re: Facttable

gautik92 — Wed, 09 Sep 2015 11:59:39 GMT

rename itembranchkey as key

join itemmaster and itembranch

join state,division,region,customeraddress with customer

Re: Facttable

avinashelite — Wed, 09 Sep 2015 12:00:49 GMT

As maxgro‌ mentioned you don't have a circular reference in the script , try to check the data once and let us know if you face any issues

Re: Facttable

Wed, 09 Sep 2015 12:26:20 GMT

the first join about Itemmaster and itemnumber is done but i dont know how to do the second one ? where can i write the join ?

Re: Facttable

gautik92 — Wed, 09 Sep 2015 12:29:13 GMT

customertable:

Load *

from customer;

leftJoin(customertable)

Load *

from state;

leftJoin(customertable)

Load *

from region;

leftJoin(customertable)

Load *

from Division;

leftJoin(customertable)

Load *

from Customeraddress;

Re: Facttable

Wed, 09 Sep 2015 12:30:27 GMT

Here is my script for now

BUDGET:

LOAD BudgetPeriod,

[Budget Amount],

BudgetYear,

[Customer Number],

textBudgetPeriod

FROM

Datasources\Datasources\BUDGET.QVD

(qvd);

CUSTOMERADDRESS:

LOAD [Address Number],

City,

Country,

[Customer Address 1],

[Customer Address 2],

[Customer Address 3],

[Customer Address 4],

State,

[Zip Code]

FROM

Datasources\Datasources\CUSTOMERADDRESS.QVD

(qvd);

CUSTOMERS:

LOAD [Address Number],

[Business Family],

[Business Unit],

Customer,

[Customer Number],

[Customer Type],

Division,

[Line of Business],

Phone,

[Region Code],

[Regional Sales Mgr],

[Search Type]

FROM

Datasources\Datasources\CUSTOMERS.QVD

(qvd);

DIVISION:

LOAD [Division Name],

Division

FROM

Datasources\Datasources\DIVISION.QVD

(qvd);

ITEMBRANCH:

LOAD [Item Number],

[Item Branch Category Code 6],

[Item Branch G/L Category],

[Item-Branch Key],

[Item Branch Reorder Quantity],

[Short Name]

FROM

Datasources\Datasources\ITEMBRANCH.QVD

(qvd);

Join

ITEMMASTER:

LOAD [Master Planning Family],

[Product Department],

[Product Group],

[Product Line],

[Product Price Group],

[Product Sub Group],

[Product Type],

[Short Name]

FROM

Datasources\Datasources\ITEMMASTER.QVD

(qvd);

PRODUCTGROUP:

LOAD [Product Group],

[Product Group Desc]

FROM

Datasources\Datasources\PRODUCTGROUP.TXT

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

PRODUCTLINE:

LOAD [Product Line],

[Line Number],

[Product Line Desc]

FROM

Datasources\Datasources\PRODUCTLINE.TXT

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

PRODUCTSUBGROUP:

LOAD [Product Sub Group],

[Product Sub Group Desc]

FROM

Datasources\Datasources\PRODUCTSUBGROUP.TXT

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

REGION:

LOAD [Region Name],

[Region Code]

FROM

Datasources\Datasources\REGION.QVD

(qvd);

SALESDETAILS:

LOAD [Actual Delivery Date],

[Address Number],

CustKey,

[Backlog Amount],

[BackOrder Amount],

DateKey,

[Discount Amount],

[Doc Type],

[Family Class],

[Invoice Date],

[Invoice Number],

[Item-Branch Key] as key ,

[Item Class],

// [Item Number],

[Line Desc 1],

// [Line Number],

[Line Type],

[List Price],

[Order Number],

[Parent Address Number],

[Payment Terms],

[Promised Delivery Date],

[Sales Amount],

[Sales Amount Based on List Price],

[Sales Cost Amount],

[Sales Margin Amount],

[Sales Price],

[Sales Quantity],

[Sales Rep],

[Sales Unit Cost Price],

[U/M],

[Unit Price]

FROM

Datasources\Datasources\SALESDETAILS.QVD

(qvd);

SALESREP:

LOAD [Sales Rep],

[Sales Rep Name]

FROM

Datasources\Datasources\SALESREP.QVD

(qvd);

STATEDESCRIPTION:

LOAD State,

state_abbr,

state_name

FROM

Datasources\Datasources\STATEDESCRIPTION.QVD

(qvd);

Re: Facttable

Wed, 09 Sep 2015 12:52:13 GMT

could you make an example with prt sc ? it will be very helpful

Re: Facttable

Wed, 09 Sep 2015 13:29:53 GMT

I got now exactly the same datamodel as you said with this script, is this correct?

CUSTOMERS:

LOAD

[Address Number],

[Business Family],

[Business Unit],

Customer,

[Customer Number],

[Customer Type],

Division,

[Line of Business],

Phone,

[Region Code],

[Regional Sales Mgr],

[Search Type]

FROM

Datasources\Datasources\CUSTOMERS.QVD

(qvd);

BUDGET:

join (CUSTOMERS)

LOAD BudgetPeriod,

[Budget Amount],

BudgetYear,

[Customer Number],

textBudgetPeriod

FROM

Datasources\Datasources\BUDGET.QVD

(qvd);

CUSTOMERADDRESS:

join (CUSTOMERS)

LOAD [Address Number],

City,

Country,

[Customer Address 1],

[Customer Address 2],

[Customer Address 3],

[Customer Address 4],

State,

[Zip Code]

FROM

Datasources\Datasources\CUSTOMERADDRESS.QVD

(qvd);

DIVISION:

join (CUSTOMERS)

LOAD [Division Name],

Division

FROM

Datasources\Datasources\DIVISION.QVD

(qvd);

ITEMBRANCH:

LOAD [Item Number],

[Item Branch Category Code 6],

[Item Branch G/L Category],

[Item-Branch Key],

[Item Branch Reorder Quantity],

[Short Name]

FROM

Datasources\Datasources\ITEMBRANCH.QVD

(qvd);

Join (ITEMBRANCH)

ITEMMASTER:

LOAD [Master Planning Family],

[Product Department],

[Product Group],

[Product Line],

[Product Price Group],

[Product Sub Group],

[Product Type],

[Short Name]

FROM

Datasources\Datasources\ITEMMASTER.QVD

(qvd);

PRODUCTGROUP:

join (ITEMBRANCH)

LOAD [Product Group],

[Product Group Desc]

FROM

Datasources\Datasources\PRODUCTGROUP.TXT

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

PRODUCTLINE:

join (ITEMBRANCH)

LOAD [Product Line],

[Line Number],

[Product Line Desc]

FROM

Datasources\Datasources\PRODUCTLINE.TXT

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

PRODUCTSUBGROUP:

join (ITEMBRANCH)

LOAD [Product Sub Group],

[Product Sub Group Desc]

FROM

Datasources\Datasources\PRODUCTSUBGROUP.TXT

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

REGION:

join (CUSTOMERS)

LOAD [Region Name],

[Region Code]

FROM

Datasources\Datasources\REGION.QVD

(qvd);

SALESDETAILS:

LOAD [Actual Delivery Date],

[Address Number],

CustKey,

[Backlog Amount],

[BackOrder Amount],

DateKey,

[Discount Amount],

[Doc Type],

[Family Class],

[Invoice Date],

[Invoice Number],

[Item-Branch Key] as key ,

[Item Class],

// [Item Number],

[Line Desc 1],

[Line Number],

[Line Type],

[List Price],

[Order Number],

[Parent Address Number],

[Payment Terms],

[Promised Delivery Date],

[Sales Amount],

[Sales Amount Based on List Price],

[Sales Cost Amount],

[Sales Margin Amount],

[Sales Price],

[Sales Quantity],

[Sales Rep],

[Sales Unit Cost Price],

[U/M],

[Unit Price]

FROM

Datasources\Datasources\SALESDETAILS.QVD

(qvd);

STATEDESCRIPTION:

join (CUSTOMERS)

LOAD State,

state_abbr,

state_name

FROM

Datasources\Datasources\STATEDESCRIPTION.QVD

(qvd);

Re: Facttable

Thu, 10 Sep 2015 10:44:32 GMT

could you please explain why the budget table is connected to Customer?

Re: Facttable

Tue, 15 Sep 2015 12:40:43 GMT

Im ready with the datamodell could you help me to start with the layout ?