Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've run into an issue trying to display the fields of a table, similar to the following, in a straight table (using V11.0.11440.0 SR2):
MyTable:
Load
1 as count,
client_id,
request,
status,
request_time, // timestamp
request_duration // interval
FROM $(vQVDFile) (qvd);
I have client_id, request, status, request_time and request_duration as the Dimensions, and sum($(vSetAnalysis)count) as the Expression.
I want to highlight a certain client_id, so I've added a Text Color expression (=if(client_id='xyz',red(),black())) to the client_id Dimension.
But when I add the Text Color expression, the normal 7GB memory footprint of the app (as viewed thru Windows Task Manager) jumps up to 54GB!
Thru trial and error I've determined that the problem is related to the fact that I have Text Color on a Dimension and at least one field that has dual internal representations (request_time and request_duration).
Observations:
- Without the Text Color expression, there's no memory problem but then there's obviously no highlighted client_id either.
- If I convert the client_id from being a chart dimension to being an expression, the problem also goes away but then the ordering of the output isn't desirable. Additionally the resulting table doesn't look good..we really need client_id to be the leading dimension.
- If I disable both the request_time AND request_duration fields, the problem goes away. If EITHER is enabled the problem returns. It doesn't matter if they're enabled as Dimensions or Expressions, it's all problematic.
- Just for fun I've tried disabling request_time and then replacing request_duration with a pure numeric Expression value that I format as an Interval in the chart properties... the problem also appears in this scenario.
Is this a bug or "expected behavior" of QlikView and is there a way to accomplish what I'm trying to do (highlighted client_id dimension, inclusion of request_time and request_duration) without the memory issue?
The color expression can be costly because of the if statement. Perhaps a pick-match combination works better, but no guarantees, it could perform even worse. pick(match(client_id,'xyz')+1,black(),red())
You should probably split request_time in a date and a time field, e.g. request_date: floor(reuest_time) and request_time: frac(request_time). Because the separate fields will have a much lower cardinality they should take up far less space (meaning less ram usage).
Another thing that might help is making sure request_duration is stored in the qvd file as a numeric value instead of a dual value. Perhaps request_time (without the date part now) should get the same treatment.
If you're using a straight table you can use client_id as an expression and drag the column to where you'd like it to show, e.g. as the first column in the table.