Showing a lot of data in a straight table

[#1]

Hi everybody,

I'm not so experienced and I have a problem so hope you can help!

I needed to summarize the sell_price by customer_id in a classical customer/orders scenario. I used a straight table (as they want to see data). I used the customer_id as only dimension and sum(sell_price) as the only expression.

That worked great even for a selection including a lot of customers.

Now my customer says he wants to see (and export to excel) all customer's data also in the same straight table.

So I added customer_address, customer_city etc. to dimensions, next to customer_id. That worked well for a small amount of records but paralized my desktop for 30 minutes for a big one (500k customers, summarizing 2M order records).

So I removed customer_address, customer_city etc. from dimensions and put them between the epressions.

That send my pc out of virtual memory.

I know I'm doing something wrong but I don't know what..

Can anybody please help me?

Thanks!

Giorgio

Ps: I'm using (and must use) Qlikview Developer 8.5 on a 3Gz bi-processor, 4 Gb Ram.

[johnw]

I'm guessing your data model looks something like this?

Customers:
customer_id, customer_address, customer_city, etc.

Orders:
order_id, customer_id, sell_price, etc.

If so, then yeah, that's pretty basic functionality, and I'd expect it to work. That said, I'm trying to sort out a similar problem - a straight table for less than 20,000 rows, no expression, and maybe 50 dimensions all from the same table. Fairly often, it simply never displays. I'm not sure why yet.

One possible problem you may have is that something went wrong loading up your data model, and you have a lot more rows than expected. If the above were my data model, I'd create a quick chart with these expressions:

count(customer_address)
count(distinct customer_address)
count(order_id)
count(distinct order_id)

If everything loaded right, I'd expect the distinct and non-distinct counts to return the same number, and for that number to be the expected number of rows in the table. Often, particularly with left joins, you can make a mistake and drastically increase the number of rows, which would show up as a difference. Happens to me all the time; I just know to look for it.

[pover]

Giorgio,

If sum(sell_price) is the formula that you are using then you can't simplify it anymore then that and you only have 3 dimensions that are apparently necessary so I would recommend you look for a server environment with more memory to produce the report.

I imagine that since customer address is unique for every customer that is probably the field that made you exceed the memory. If the out of virtual memory error is sent for a report that had once was ok, restart QlikView to completely clear the memory QlikView is using.

Regards.

[pover]

I don't know if there is problem with this method, but for that information I've gone to Document Properties -> Tables and then looked at the list of fields to see the numbers of values and distinct values of each field.

I've used it to see if there is a column with little data redundency that I might be able to delete and make the model more efficient. I've never looked used it to detect possible problems with joins.

[johnw]

Karl Pover wrote:I don't know if there is problem with this method, but for that information I've gone to Document Properties -> Tables and then looked at the list of fields to see the numbers of values and distinct values of each field.

That should work. Somehow I never thought of using it that way. Obvious in hindsight. Ah, well. Thanks!

[#6]

Thank you very much to both of you.

I do have left joins. And I discovered I have a little more data than expected (not very much).

The strange thing is that it is not the amount of data that causes the problem but the number of dimensions: in fact if i put all the data in a single dimension concatenating all the fields, it slows down a bit but it works..

Giorgio