topic Re: Data Model Northwind in QlikView

Data Model Northwind

Mon, 31 Mar 2014 22:26:01 GMT

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,

FROM Shippers;

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

Thanks

Ankit

Re: Data Model Northwind

Mon, 31 Mar 2014 22:43:25 GMT

Simply add a Qualify *; statement to start with.

But give a meaningful table name before every select statement.

Re: Data Model Northwind

Tue, 01 Apr 2014 00:01:38 GMT

Hi Ankit,

Qlikview joins automatically two tables when 1 column has the exact same name in the two tables.
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.

Re: Data Model Northwind

Tue, 01 Apr 2014 06:12:42 GMT

Thanks a Lot:)

Re: Data Model Northwind

Tue, 01 Apr 2014 06:13:01 GMT

Thank you

Re: Data Model Northwind

its_anandrjs — Tue, 01 Apr 2014 06:18:42 GMT

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

Re: Data Model Northwind

kangaroomac — Tue, 01 Apr 2014 08:57:37 GMT

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

Re: Re: Data Model Northwind

Tue, 01 Apr 2014 19:59:40 GMT

Hi Anand,

Thanks for the reply.

Please find the required data as an attachment.

Thanks

Ankit

Re: Re: Data Model Northwind

Tue, 01 Apr 2014 20:01:42 GMT

Hi Anand,

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

Thanks

Ankit

Re: Re: Data Model Northwind

its_anandrjs — Tue, 01 Apr 2014 21:00:21 GMT

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

Re: Data Model Northwind

Wed, 02 Apr 2014 06:30:02 GMT

Thanks a lot

Re: Data Model Northwind

its_anandrjs — Wed, 02 Apr 2014 07:00:41 GMT

Hi,

If you got correct answer from the thread so mark it Correct/Helpful for reference

Thanks & Regards