Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data Model Doubt

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);

1 Solution

Accepted Solutions
maxgro
MVP
MVP

9 Replies
Anonymous
Not applicable
Author

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.

Colin-Albert

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

Anonymous
Not applicable
Author

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.

ashfaq_haseeb
Champion III
Champion III

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

Not applicable
Author

Could anybody modify the entire script and provide.

maxgro
MVP
MVP

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

Not applicable
Author

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

maxgro
MVP
MVP

PFA

1.jpg

Not applicable
Author

Thank you All.