topic Loop problem in QlikView

Loop problem

Tue, 13 Jul 2010 10:03:44 GMT

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

Loop problem

Tue, 13 Jul 2010 10:11:14 GMT

Hi Nicolas,

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

Loop problem

sparur — Tue, 13 Jul 2010 10:11:28 GMT

you should resolve your loop data model.

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

Loop problem

Tue, 13 Jul 2010 10:19:21 GMT


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...

Loop problem

Tue, 13 Jul 2010 10:52:37 GMT

- 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)

Loop problem

Tue, 13 Jul 2010 10:53:28 GMT

(2nd file)

Loop problem

johnw — Wed, 14 Jul 2010 00:12:06 GMT

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.