Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data Model Northwind

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

11 Replies
Not applicable
Author

Simply add a Qualify *; statement to start with.

But give a meaningful table name before every select statement.

Not applicable
Author

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.

Not applicable
Author

Thanks a Lot:)

Not applicable
Author

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
Partner
Partner

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

Not applicable
Author

Hi Anand,

Thanks for the reply.

Please find the required data as an attachment.

Thanks

Ankit

Not applicable
Author

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