Loop, Loosely Coupled, Direction of keys

Sun, 22 Sep 2013 03:01:23 GMT

Hi Folks. I am facing a situation in qlikview where data between two tables and within a table is not showing. Let me explain with screenprints to make the situation clear.

Following is my current internal table layout as generated by qlikview:

Following is the external table structure:

Example 1 – Data Shows. Now I have selected the above fields from following tables:

* Product ID, Net Sales From Order Details Table

* Product Name From Product Table

* Category Name, Description From Category Table

Now the above example (example 1) works well given we have “solid” lines that are linking the ids in these tables (look at internal table view – Order Detail links to Product which links to Category)

Now the following is an example where data between tables are not linking with following selections:

Example 2:

* Order id, net sales from order Table

* Order Id, Customer Id, Employee id from order details Table

This above situation (example 2) is only happening with the order table and doesn’t happen with any other table.

Following is my qlikview script:

MS EXCEL DATA EMPLOYEE TABLE)

//SET ThousandSep=',';
//SET DecimalSep='.';
//SET MoneyThousandSep=',';
//SET MoneyDecimalSep='.';
//SET MoneyFormat='Rs. #,##0.00;Rs. -#,##0.00';
//SET TimeFormat='hh:mm:ss';
//SET DateFormat='DD-MM-YYYY';
//SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';
//SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
//SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
LOAD EmpID as EmployeeID,
     [Last Name],
     [First Name],
     Title,
     [Hire Date],
     Office,
     Extension,
     [Reports To],
     [Year Salary],
     Today() - [Hire Date] As DaysWorked,
     Floor((Today() - [Hire Date])/365) As YearsWorked
FROM

(biff, embedded labels, table is Employee$);
LOAD [Office] as Office,
     [Address] as EmOfAddre,
     [Postal Code] as EmOfZip,
     [City] as EmOfCity,
     [StateProvince] as EmOfState,
     [Phone] as EmOfPhon,
     [Fax] as EmOfFax,
     [Country] as EmOfCountry
FROM

(biff, embedded labels, table is Office$);

MS ACCESS DATA SCRIPT(ALL OTHER TABLES)

ODBC CONNECT TO [MS Access
Database;DBQ=C:\Users\Uday\Desktop\Qlikview Project\Files\QWT.mdb];
//--------Start Multiple Select Statements ------
[Orders]:
SQL SELECT *
FROM Orders;

//Start
of selection from order detail table
[Order
Details]:
LOAD *,
[UnitPrice] * [Quantity] as [Gross Sales],
([UnitPrice] * [Quantity])*[Discount] as [Discount Amount],
(([UnitPrice] * [Quantity]) - ([UnitPrice] * [Quantity])*[Discount]) as [Net Sales];

LOAD
[OrderID],
[ProductID],
[UnitPrice],
[Quantity],
[Discount];
SQL Select *
FROM `Order Details`;
//End
of Selection from order detail table

//Start
of selection from Products table
[Products]:
LOAD *,
([UnitCost] * [UnitsInStock]) as [Net Inventory
Cost];

LOAD
[ProductID],
[ProductName],
[SupplierID],
[CategoryID],
[QuantityPerUnit],
[UnitCost],
[UnitPrice] as ProductUnitPrice,
[UnitsInStock],
[UnitsOnOrder];

SQL SELECT *
FROM Products;
//End
of Selection from products table

SQL SELECT *
FROM Categories;

SQL SELECT *
FROM Customers;

SQL SELECT *
FROM Shippers;
//--------
End Multiple Select Statements ------

Following is the screenprint from settings > document properties and as you can see that orders table is
loosely coupled. I tried to uncheck this box, however this is not working:

At this stage I am little confused on concept of “associative technology” since the links should work given fields names are same.

I have attempted to explore the following possible solutions but I feel they may not work and honestly I need more technical knowledge on qlikview scripting:

Solution 1 – Somehow remove the lose coupling on the orders table

Solution 2 – Rename all fields with Qualification – Con, I believe if did that then how would data link between fields

Solution 3 – Somehow change the dashed lines to solid lines and with solid dots rather than arrows. However, I have not been able to locate any information on how to undertake this aspect.

Solution 4 – Build star schema – I disagree with building a Fact table surrounding with dimension tables given we are working in associative technology model

I believe this should a fairly simple change.

Thank you all for reading this discussion and providing
support.

Cheers

Uday

Re: Loop, Loosely Coupled, Direction of keys

Tue, 31 Dec 2013 14:46:11 GMT

The main issue i see here is the circular loop between Shippers, Orders, Customers tables. The circular reference is causing the loosely coupled relationship. Qlikview won't handle a circular reference properly which is why you are seeing the issues you are. remove the key from one of those tables so you access shippers from ORders or customers, but not both (or whichever link you want to break).

Re: Loop, Loosely Coupled, Direction of keys

Peter_Cammaert — Tue, 31 Dec 2013 15:08:27 GMT

The field CompanyNam in logical table Shippers contains the name of the Shipper, right?

Then change the Shippers table load code in the MS Access script into this:

SQL SELECT ShipperID, CompanyNam AS ShipperCompanyNam
FROM Shippers;

Best,

Peter

topic Loop, Loosely Coupled, Direction of keys in QlikView

Loop, Loosely Coupled, Direction of keys

Re: Loop, Loosely Coupled, Direction of keys

Re: Loop, Loosely Coupled, Direction of keys