topic Re: Data Model Doubt in QlikView

Data Model Doubt

Sun, 01 Feb 2015 16:59:53 GMT

Hi All,

Please correct me this data model.

Thank you.

Branch:
LOAD BranchID,
     Branch,
     CityID
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is Branch);

City:
LOAD CityID,
     City,
     CountryID
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is City);

Country:
LOAD CountryID,
     Country,
     RegionID
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is Country);

Customer:
LOAD CustomerID,
     Customer,
     CityID,
     ContactNumber
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is Customer);

Product:
LOAD ProductID,
     [Product Name]
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is Product);

Region:
LOAD RegionID,
     Region
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is Region);

Salesman:
LOAD SalesmanID as Salesman_ID,
     Salesman,
     CityID,
     ContactNumber as [Salesman ContactNumber]
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is Salesman);

Transaction:
LOAD TransactionID,
     TransactionDate,
     ProductID,
     CustomerID,
     SalesmanID,
     BranchID as Branch_ID,
     SalesPrice,
     CostPrice,
     Discount,
     Qty
FROM
C:\QV\data.xlsx
(ooxml, embedded labels, table is Transaction);

Re: Data Model Doubt

Anonymous — Sun, 01 Feb 2015 17:11:44 GMT

I'd suggest you consolidate it into a single fact table. Personally I'd use a series of ApplyMap() 's, but others may prefer joins.

Re: Data Model Doubt

Colin-Albert — Sun, 01 Feb 2015 17:32:06 GMT

Create a series of Mapping Tables for Branch, City, Customer, Salesman etc. Then you can use Applymap to bring this data into your transactions table.

See this post for more details.Don't join - use Applymap instead

Re: Data Model Doubt

Anonymous — Mon, 02 Feb 2015 05:12:31 GMT

Applymap is the best approach as suggested by Colin,

you dont need to load higher level hierarchy in the same table .

Ex.

City:

LOAD CityID,

City,

CountryID

No need to load 'CountryID' on this table .

(

City:

LOAD CityID,

City)

load all the table with two columns (IDfield and Name)

You can later load it in other applymap table and directly populate Country in Fact table using Applymap function.

Re: Data Model Doubt

ashfaq_haseeb — Mon, 02 Feb 2015 05:24:15 GMT

Hi,

As suggested by all go with Applymap()

In case you have more then 1 description against any ID then ApplyMap() will not work here.

Regards

ASHFAQ

Re: Data Model Doubt

Mon, 02 Feb 2015 15:36:23 GMT

Could anybody modify the entire script and provide.

Re: Data Model Doubt

maxgro — Mon, 02 Feb 2015 15:44:03 GMT

could you post some data as all your tables come from one file C:\QV\data.xlsx?

is much easier to answer with some data to work

Re: Data Model Doubt

Mon, 02 Feb 2015 16:25:53 GMT

Enclosed the xls file in the first post. Please advise.

Re: Data Model Doubt

maxgro — Mon, 02 Feb 2015 16:46:59 GMT

PFA

Re: Data Model Doubt

Mon, 02 Feb 2015 16:57:35 GMT

Thank you All.