Linking Problem causes Loops

[#1]

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

[#2]

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

Stephen

[erichshiino]

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

[#4]

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

[erichshiino · Accepted solution]

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

[#6]

Perfect. Thanks Erich.