Looping issue when loading multiple tables

Wed, 25 Jun 2014 03:28:57 GMT

Hi, I am a QV newbie, trying to load the tables below into QV to generate some reports pertaining to Sales results (Actual vs Planned & also Actual vs Last Year). So here are the tables:

1.) ACTUAL SALES

2.) BP SALES <==(Business Plan)

3.) Product Master

4.) Customer Master

5.) Currency Rates (where the months matches both ACTUAL & BP SALES, we just need to know which month's rate to convert intoa base currenc

Runs the script after connecting the tables via OBDC to my local MS Access as below, I was prompted with

"One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog."

And when I total the ACTUAL SALES, there are variances found, which wasn't there before I introduced the BP SALES Table into the whole picture.

I have 3 questions:

1.) Can I change a field name right after the [SQL SECLECT * ] using Oldfieldname as Newfieldname? Doesn't seem to work. I have no problem change field names if it's a direct upload from a tablefield where I can see all the fields loaded, but in this case, there are no fields shown since it's SELECT *

2.) Is the loop issue causing some of my total ACTUAL_Amt(JPY) in the ACTUAL SALES to appear lower than it should? I don't see any problem when I sum the BP_Amt(Local Currency) from the BP_SALES.

3.) What should I do to avoid the looping issue?

My Script:

***********************************************************************************************************************************

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

ODBC CONNECT TO [MS Access Database Ronnie Test;DBQ=C:\USERS\RONNIE\DESKTOP\ACCESS DB\NSC_ACTUAL_SALES.accdb];
//-------- Start Multiple Select Statements ------
//-------- Start Multiple Select Statements ------

NSC_ACTUAL_SALES:
SQL SELECT *
FROM `T001D_NSC_ACTUAL_SALES_06242014`;

NSC_BP_SALES:
SQL SELECT *
FROM `T002A_NSC_BPSALES_06242014`;

CURRENCY_RATE:
SQL SELECT *
FROM `T_Master002_Currency_Rate_06242014`;

NSC_CUSTOMER:
SQL SELECT *
FROM `T_Master003_NSC_Customer_06182014`;

PRODUCT:
SQL SELECT *
FROM `T_Master004_Product_06202014`;
//-------- End Multiple Select Statements ------
//-------- End Multiple Select Statements ------

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

***********************************************************************************************************************************

My Data Model:

Re: Looping issue when loading multiple tables

Wed, 25 Jun 2014 03:59:31 GMT

Hi, I think I have solved question 2 by eliminating Product Master out of the data model. However, that takes away ability to allow the BP & ACTUAL Sales tables to utilize some common attributes in the PRODUCT master.

ie: In BP, the key is Cat4 & the key in ACTUAL is MODEL to link to the PRODUCT master, I need to use Cat4 for both BP & ACTUAL as the common attribute to display as dimension in a chart. Let's say when I pick a particular month & year, it should utilize the Cat4 from BP and also Cat4 from ACTUAL(linked to a PRODUCT master through MODEL) that related to the month/year selected. However, I can't get Qlikview to do that correctly through the previous data model.

What should I do to make this linkage?

topic Looping issue when loading multiple tables in QlikView

Looping issue when loading multiple tables

Re: Looping issue when loading multiple tables