topic Linking Problem causes Loops in QlikView

Linking Problem causes Loops

Tue, 24 May 2011 22:13:25 GMT

I have three tables..

Table 1 - Billing Header

SalesmanCode

BillingNumber

CustomerNumber

BillingDate (date of Invoice

Table 2 - Billing Detail

BillingNumber

MaterialCode

ProductClassCode

BillingAmount

Tabel 3 - Budget (Note that SalesmanCode and CustomerNumber are the same as in Table 1 and ProductClassCode is isame as in Table 2)

SalesmanCode

CustomerNumber

ProductClassCode

Date (all dates are first of the month)

BudgetAmount

Table 1 and Table 2 links with BillingNumber and I get accurate results. When I add Table 3 I get the message about "One or more loops have been detected . . . . . . In the Table Viewer I get dashed line on ProductClassCode (Table 2 to Table 3) and dashed lines on BillingNumber (Table 1 to Table2). When the users selects a SalesmanCode or CustomerNumber and a ProductClassCode they should see actual sales for the month and the budget amount for that ProductClass. How do I get the Budget Table to link to the Billing tables with causing the "loop" problem and get the results I need.

Thanks,

Stephen

Re: Linking Problem causes Loops

Wed, 25 May 2011 17:23:31 GMT

I have attached test data to show an example of the problem.

Stephen

Re: Linking Problem causes Loops

erichshiino — Wed, 25 May 2011 19:07:54 GMT

Hi,

You got a circular reference. The results for selections in this case are (kind of) unpredictable.. You need to change your data model.

My suggestion is:

Fact:

LOAD * INLINE [

SalesmanCode, BillingNumber, CustomerNumber, BillingMonth

SM1,Invoice1,CN1,1

SM1,Invoice2,CN2,1

SM1,Invoice3,CN3,1

SM2,Invoice4,CN4,1

SM2,Invoice5,CN5,1

SM3,Invoice6,CN6,1

SM3,Invoice7,CN7,1

SM1,Invoice8,CN1,2

SM2,Invoice9,CN4,2

SM5,Invoice10,CN77,1

SM1,Invoice11,CN2,2

];

left join(Fact)

Load * INLINE [

BillingNumber, MaterialCode, ProductClassCode, BillingAmount

Invoice1,abc,RAM,1223.44

Invoice2,def,STRM,456.54

Invoice3,ghi,STRM,789.54

Invoice4,jkl,PE,684.20

Invoice5,mno,PC,78.25

Invoice6,pqr,URL,788.48

Invoice7,stu,URM,751.25

Invoice8,vwx,RAM,425.00

Invoice9,xyz,PE,323.42

Invoice10,CBA,FE,542.97

Invoice11,FED,STRM.987.25

];

concatenate(Fact)

LOAD * INLINE [

SalesmanCode, CustomerNumber ,BillingMonth,ProductClassCode,BudgetAmount

SM1,CN1,1,RAM,1000.25

SM1,CN1,2,RAM,825

SM1,CN2,1,STRM,500.00

SM1,CN3,1,STRM,250.00

SM2,CN4,1,PE,750.00

SM2,CN5,1,PC,450

SM3,CN6,1,URL,790

SM3,CN7,1,URM,625.00

SM4,CN99,1,ARK,20000

];

Re: Linking Problem causes Loops

Wed, 25 May 2011 22:29:16 GMT

Erich,

Great, works in the example I gave. However, my tables are a bit more complicated when loading.

Table 1 left joins two other tables and then has a mapping that Table 2 uses. Table 2 also has other tables that are left joined. So how to I left join table 1 to table 2 after the loading and mapping stuff runs?

Stephen

Re: Linking Problem causes Loops

erichshiino — Thu, 26 May 2011 03:41:34 GMT

After the creation of all tables

you can make

left join (Table1)

Load * resident Table2;

drop table Table2;

concatenate(Table1)

load * resident Table3;

Drop table Table3;

Rgds,

Erich

Re: Linking Problem causes Loops

Thu, 26 May 2011 15:53:56 GMT

Perfect. Thanks Erich.