Loop problem

[#1]

Good day,

Here is my model :

As you can see, I have a loop problem.

When I have this kind of problem, I usually do the following :

- create a master table for "Products" (or "Cutomers")
- use a "if" statement in my expression to make the link

For exemple : if a want to make a graph showing "sales" and "budget" by "product", I use the dimension "MasterProduct",

For "sales" I use :

=sum(if(Sales_ProductID = MasterProductID, Amount))

For "budget" I use :

=sum(if(Budget_ProductType = MasterProductType, Budget))

But, I this case, I have more than 5'000'000 lines in "Sales" and many lines in "Customers" and "Product". QV can't make the calculation (out of memory, or application freezes).

Do you have an idea of another solution ?

Thank you,

Nicolas

[#2]

Hi Nicolas,

I have faced this loop problem. So, for this, i have renamed the fields, which create loosen table.

[sparur]

you should resolve your loop data model.

For example, you can rename Product type in Budget table.

[#4]

sparur wrote:
For example, you can rename Product type in Budget table. <div></div>

Yes, this is what I usually do when I face this kind of problem :

I rename "ProductType" to "Budget_ProductType" and use a "if" statement in my expression (like said in my first post).

The problem is I have to many rows, the "if" is too long to compute...

[#5]

- Link both "Sales" and "Budget" gives loop.

- Disconnecting only 1 table "Budget" gives wrong results (cf. attached file "loop problem - budget disconnected.qvw").

Budget lines should be "4".

- That's why I use the island approach disconnecting the 2 tables "Sales" and "Budget" (cf. attached file "loop problem - sales & budget disconnected.qvw").

Here, the results are OK.

BUT, my problem is that with 5'000'000 lines in "Sales", the graph cannot refresh !!!

(--> my first post)

[#6]

(2nd file)

[johnw]

If you change the field name back to ProductType in the Budget table, left join your product table onto your sales table, then drop the sales table, the loop goes away and you get a synthetic key on CustID and ProductType. On the surface that sounds right to me. Then remove the if() from your Budget expression, and you get the totals of 4 and 4. Not sure if everything else will be like you want it, but that part seems to be, and it should be efficient for large data sets.