topic Re: Paint Columns of Pivot table - Null values problem in QlikView

Paint Columns of Pivot table - Null values problem

Alma1234 — Sat, 16 Nov 2024 20:02:11 GMT

Hello,

I have a pivot table where i need to color all actual columns in gray.

I added an expression under 'Background color' as

=if(Dimension2='Actual,RGB(221,221,221)) but the Null values remain White.

Is there a way to change null values in script to a number. i tried ifnull then 0 but it didn't work.

Any idea will be appreciated.

Regards

Re: Paint Columns of Pivot table - Null values problem

Anil_Babu_Samineni — Sun, 22 Sep 2019 11:20:29 GMT

Try this way

NullAsValue Dimension2;

Set NullValue = 'NULL';

and, expression should be

=if(Match(Dimension2, 'Actual', 'NULL'),RGB(221,221,221))

But, in your expression I am not finding anything for null values as color hence define as

=if(Dimension2='Actual' or IsNull(Dimensio2),RGB(221,221,221))

Re: Paint Columns of Pivot table - Null values problem

Alma1234 — Sun, 22 Sep 2019 11:47:51 GMT

Hi,

Thanks for the answer.

I have null values for all dimensions so it is not working.

If i paint null values than i get gray in the dimensions Budget and Last Month as well.

Cheers,

Re: Paint Columns of Pivot table - Null values problem

Anil_Babu_Samineni — Sun, 22 Sep 2019 11:57:24 GMT

I feel those are not real null values, Hence i may suggest this way as always

NullAsValue Dimension2, Dim1, Dim3, Dim4...;

Set NullValue = 'NULL';

Re: Paint Columns of Pivot table - Null values problem

Alma1234 — Sun, 22 Sep 2019 12:17:01 GMT

You are right. some are Null values and some are missing values.

What do you mean by

NullAsValue Dimension2, Dim1, Dim3, Dim4...;?

I have 4 dimensions, to write them on script as variable?

Re: Paint Columns of Pivot table - Null values problem

Anil_Babu_Samineni — Sun, 22 Sep 2019 12:59:31 GMT

This statement will change non-values to real null values. You can mention as it is like

NullAsValue Dimension2, Dim1, Dim3, Dim4...;

Set NulValue = 'NULL';

Fact1:

Load * From T1;

Fact 2:

Load * From T2;

etc...

Re: Paint Columns of Pivot table - Null values problem

Alma1234 — Mon, 23 Sep 2019 08:13:20 GMT

Hi,

You are right. It solved the null values.

The other rows are non-exist rows, for this i guess i can only fill the rows on script.

Thanks,