topic Re: Bad performance when coloring cells in pivot table in QlikView

Bad performance when coloring cells in pivot table

Thu, 23 Apr 2015 16:45:28 GMT

Hi everyone,

I have a pivot table that is working OK, but when I try to apply a background color to the cells based based on a dimension value, it becomes slow and uses a lot of RAM(from 160 mb to 3.6 GB).

Has anyone had this issue before?

Please find attached my sample file. Right now the color expression is commented under the measure background color expression.

Thanks in advance.

Daniel

Re: Bad performance when coloring cells in pivot table

Fri, 24 Apr 2015 20:44:09 GMT

same sample file(zip format).

Re: Bad performance when coloring cells in pivot table

Gysbert_Wassenaar — Sat, 25 Apr 2015 08:36:17 GMT

Try loading the colors in a seperate table:

Colors:

LOAD CategoryID as ID,

rgb(R,G,B) as ColorRGB

RESIDENT Category

Then use this expression to get the colors: FieldValue('ColorRGB', CategoryID)

Re: Bad performance when coloring cells in pivot table

SergeyMak — Sat, 25 Apr 2015 11:13:10 GMT

I Think you have some problem with data. Your fact table has almost 27 ml rows.

It looks like you don't have values in Account field in Fact table

Regards,

Sergey

Re: Bad performance when coloring cells in pivot table

Mon, 27 Apr 2015 16:59:10 GMT

Hi Gysbert,

It seems to be working.

However, if I have repeated colors in my Colors table I get the wrong colors.

Is there a way to get the ColorRGB using the ID in the Colors table? The second parameter of FieldValue is the value found in the position n(by load order) and if I have repeated values I wont get a one-to-one relationship between ID and ColorRGB.

Best regards,

Daniel

Re: Bad performance when coloring cells in pivot table

Gysbert_Wassenaar — Mon, 27 Apr 2015 17:23:15 GMT

No, for the fieldvalue function to work there should be a one-to-one relationship to ID and color. Otherwise you'll have to use something like the pick function: pick(CategoryID, $(=concat(ColorRGB , ',', ColorID)))

Re: Bad performance when coloring cells in pivot table

Mon, 27 Apr 2015 17:31:07 GMT

Hi Gysbert,

Yes, I have a list of Ids and each Id has one color. Like this:

CategoryColors:

LOAD ColorID, RGB(R,G,B) as ColorRGB INLINE [

ColorID, R, G, B

1, 192,192,192

2, 255, 255, 255

3, 255, 255, 255

4, 153, 204, 0

5, 255, 255, 255

6, 255, 255, 255

7, 153, 204, 0

8, 153, 204, 0

9, 255, 255, 255

10, 255, 255, 255

11, 255, 255, 255

];

However, If I try to lookup FieldValue('ColorRGB', 3), it returns RGB(153, 204, 0) instead of RGB(255, 255, 255).

Also, FieldValue('ColorRGB', 4) returns NULL. The FieldValue second parameter does not use the ColorID but the load order.

Best regards,

Daniel

Re: Bad performance when coloring cells in pivot table

Gysbert_Wassenaar — Mon, 27 Apr 2015 19:21:47 GMT

What you can do is add a ColorID field to the Category table:

Category:

LOAD *, autonumber(Color) as ColorID;

LOAD CategoryID,

Category,

rgb(R,G,B) as Color

FROM [Category.qvd] (qvd);

STORE Category into Category.qvd;

Colors:

LOAD ColorID as ID,

rgb(R,G,B) as ColorRGB

FROM [Category.qvd] (qvd);

Then you can use the fieldvalue function with ColorID as second parameter.