Data Model Northwind

[#1]

Hi,

I am new to QlikView and i am trying to prepare a data model using Access file.

When i am trying to put the below in Script i am getting lots of Synthetic Keys.

Can someone tell me how to go ahead with that.

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\Users\DELL\Downloads\t1.accdb];

//-------- Start Multiple Select Statements ------

SQL SELECT Category,

    CategoryID

FROM Categories;

SQL SELECT Address,

    City,

    Company,

    ContactName,

    'CUST_ID'

FROM Customers;

SQL SELECT Address,

    BusinessPhone,

    City

FROM CustomersExtended;

SQL SELECT Available,

    InventoryID,

    ProductID,

    ProductName,

    SupplierID

FROM InventorySummary;

SQL SELECT CustomerID as CUST_ID,

    EmployeeID

FROM Orders;

SQL SELECT `PRODUCT_ID`,

    ProductName

FROM ProductList;

SQL SELECT Address,

    ContactName,

    CountryRegion,

    `Supplier_ID`

FROM Suppliers;

SQL SELECT Address,

    Company,

    CountryRegion,

    ID

FROM Shippers;

//-------- End Multiple Select Statements ------

Thanks

Ankit

[#2]

Simply add a Qualify *; statement to start with.

But give a meaningful table name before every select statement.

[#3]

Hi Ankit,

1. Qlikview joins automatically two tables when 1 column has the exact same name in the two tables.
2. A synthetic key is created to break circular references between tables. I.E: Having two names of columns in multiple tables.

SQL SELECT Address,

    City,

    Company,

    ContactName,

    'CUST_ID'

FROM Customers;

SQL SELECT Address,

    BusinessPhone,

    City

FROM CustomersExtended;

There are multiple ways to manage those synthetic keys: Joins, Concatenates, Link Tables or simply using a prefix on each tables' columns to have different names.

It is up to you to decide which of those solutions is the one you need.

[#4]

Thanks a Lot:)

[#5]

Thank you

[its_anandrjs]

Rename if more than key is available in two tables or make composite keys. Also check there is circular reference or not by pressing Ctrl + T if there so remove them. Can you provide me the access file of northwind so i provide you the load script for that.

Thanks & Regards

[kangaroomac]

Try this (I've highlighted the changes by making them bold😞

(Please note I've edited your script just by looking at it, so you'll have to go to the table viewer (CTRL + T) and make sure all synthetic keys are removed and all tables are linked)

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\Users\DELL\Downloads\t1.accdb];

//-------- Start Multiple Select Statements ------

SQL SELECT Category,

    CategoryID

FROM Categories;

SQL SELECT Address,

    City,

    Company,

    ContactName,

    'CUST_ID'

FROM Customers;

SQL SELECT Address,

    BusinessPhone,

   // City

  // Alternatively: City AS City_Cust_Extended

FROM CustomersExtended;

SQL SELECT Available,

    InventoryID,

    ProductID,

    ProductName,

    SupplierID AS Supplier_ID

FROM InventorySummary;

SQL SELECT CustomerID as CUST_ID,

    EmployeeID

FROM Orders;

SQL SELECT `PRODUCT_ID`,

    ProductName

FROM ProductList;

SQL SELECT Address,

    ContactName,

    CountryRegion,

    `Supplier_ID`

FROM Suppliers;

SQL SELECT Address,

    Company,

    CountryRegion,

    ID AS Shipper_ID

FROM Shippers;

//-------- End Multiple Select Statements ------

[#8]

Hi Anand,

Thanks for the reply.

Please find the required data as an attachment.

Thanks

Ankit

[#9]

Hi Anand,

Also if you can help me that can we make the connection of all the tables through ID.

Thanks

Ankit

[its_anandrjs]

Hi,

Try to load your model like below and copy paste this script in your file and check data model by pressing Ctrl + T

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\Users\DELL\Downloads\t1.accdb];

//-------- Start Multiple Select Statements ------

SQL SELECT Category,

    CategoryID

FROM Categories;

SQL SELECT Address,

    City,

    Company,

    ContactName,

    `Customer_ID`

FROM Customers;

SQL SELECT Address,

    BusinessPhone

   // City

  // Alternatively: City AS City_Cust_Extended

FROM CustomersExtended;

SQL SELECT Available,

    InventoryID,

    ProductID as Product_ID,

    ProductName as Product_Name,

    SupplierID AS Supplier_ID

FROM InventorySummary;

SQL SELECT CustomerID as Customer_ID,

    EmployeeID,

    ShipperID

FROM Orders;

SQL SELECT `PRODUCT_ID`,

    ProductName

FROM ProductList;

SQL SELECT

    //Address,

    ContactName,

    //CountryRegion,

    `Supplier_ID`

FROM Suppliers;

SQL SELECT

    //Address,

    //Company,

    CountryRegion,

    `Shipper_ID` as ShipperID

FROM Shippers;

SQL SELECT CategoryID,

    `Product_ID`,

    ProductCode,

    ProductName

FROM Products;

Hope this helps

Thanks & Regards