topic Re: Re: Any alternatives for empty expression in a Pivot table ? in QlikView

Any alternatives for empty expression in a Pivot table ?

Thu, 12 Sep 2013 01:51:24 GMT

Hi All,

I have created a Pivot Table with 3 dimensions. However, Pivot table requires an expression which I have created an empty one in order to show the data(in text form).

The result is what I wanted with one minor issue. There is a empty row at the end of each row for each group. Below illustrate an example with highlighted rows:

You may ask why I don want to create an expression. This is because I have tried to create one but the data displayed is not what I want. Eg. If I create an expression, the "this is a comment 2" will not be shown. They will show only one comment per qns instead which to me is loss of data displayed.

Any one has encounter similar problem before that can help on this ?

Would appreciate some help here.

thanks!

Re: Any alternatives for empty expression in a Pivot table ?

marcus_sommer — Thu, 12 Sep 2013 11:32:25 GMT

I don't sure if I have you understood, but perhaps the attachment is helpful for you.

- Marcus

Re: Any alternatives for empty expression in a Pivot table ?

Anonymous — Thu, 12 Sep 2013 12:04:05 GMT

Hi,

In the properties of your pivot table, in "dimensions", for each dimension you have an option to delete null values.

Re: Any alternatives for empty expression in a Pivot table ?

nizamsha — Thu, 12 Sep 2013 12:21:09 GMT

in presentation tab un check the show partical sum

Re: Any alternatives for empty expression in a Pivot table ?

Fri, 13 Sep 2013 08:10:06 GMT

Hi,

possible to paste the code as I am using Personal edition and awaiting for Enterprise version to arrive.

Thanks!

Re: Any alternatives for empty expression in a Pivot table ?

Mon, 16 Sep 2013 01:31:15 GMT

Hi Marcus,

possible to paste the code as I am using Personal edition and awaiting for Enterprise version to arrive.

Thanks!

Re: Any alternatives for empty expression in a Pivot table ?

Mon, 16 Sep 2013 01:31:30 GMT

Hi Wassenaar,

possible to paste the code as I am using Personal edition and awaiting for Enterprise version to arrive.

Thanks!

Re: Any alternatives for empty expression in a Pivot table ?

Mon, 16 Sep 2013 02:14:38 GMT

Hi,

Below are the codes for better understanding:

First Dimension

EOR_Subject_Name

Second Calculated Dimension

=If(Len(EOR_Questions)>14 and WildMatch(EOR_Questions,'*27*','*28*','*29*')>0 ,EOR_Questions)

Third Calculated Dimension

=If(Len(EOR_Questions)>14 and WildMatch(EOR_Questions,'*27*','*28*','*29*')>0 ,Data)

Expression

= ' '

The first post where there is value of NA is due to null value. Under Presentation Tab, I have input 'NA' for null values and missing symbol. Even though I checked 'Suppress Missing Values' OR 'Suppress Zero-Values' but to no valid.

Any one can help on this.

Re: Re: Any alternatives for empty expression in a Pivot table ?

marcus_sommer — Mon, 16 Sep 2013 06:55:38 GMT

I don't still understand what is your aim - I assume that your confusion caused only about the settings partial sums and showing null-values.

- Marcus

Re: Re: Any alternatives for empty expression in a Pivot table ?

Mon, 16 Sep 2013 08:30:28 GMT

Dear Marcus,

AIM: To remove the highlighted rows in the shown diagram.

Partial Sums and Null Values are turned off already. My data is text, not numeric.

Below is the codes that I have written to show the above data. If I used the calculated dimension as Expression. It will not show the result that I wanted. Only solution is to use them as calculated dimensions then the correct data will be shown. However, to show the desired result, an empty expression is to be needed. This in turn create the highlighted rows (in Yellow) which I don want.

The problem now is to get rid of the highlighted rows.

Hope this clears up, thanks!

First Dimension

EOR_Subject_Name

Second Calculated Dimension

=If(Len(EOR_Questions)>14 and WildMatch(EOR_Questions,'*27*','*28*','*29*')>0 ,EOR_Questions)

Third Calculated Dimension

=If(Len(EOR_Questions)>14 and WildMatch(EOR_Questions,'*27*','*28*','*29*')>0 ,Data)

Expression

= ' '

Re: Re: Re: Any alternatives for empty expression in a Pivot table ?

marcus_sommer — Mon, 16 Sep 2013 10:04:30 GMT

Try this:

- Marcus

Re: Any alternatives for empty expression in a Pivot table ?

Wed, 18 Sep 2013 06:28:00 GMT

Hi Marcus,

Your above solution did indeed solve part of my problem. I checked the check box for all 3 dimensions. It eliminates the 'NA' under Questions column.

Screenshot below to illustrate the example:

Now left the empty rows under the data column. I also discover that one of the subjects that has no data still been displayed out. Screenshot as per below.

It is due to calculated dimension that I have that resulted in this behavior ?

Thanks in advance!

Re: Any alternatives for empty expression in a Pivot table ?

jonathandienst — Wed, 18 Sep 2013 06:59:28 GMT

I think your problem is that you are using indent mode for your pivot, and have partial sums enabled. Go to the chart properties | Style tab and clear the check box next to indent mode. Then go to Presentations, and turn off the partial sums on both dimensions. Then, if you prefer, you can check the indent mode option again. That should remove the yellow lines.

Hope that helps

Jonathan

Re: Any alternatives for empty expression in a Pivot table ?

Wed, 18 Sep 2013 07:11:10 GMT

Hi Jonathan,

Indent is off by default. I have also turned off the partial sums but to no valid.

This is why I am thinking if my dimension is written correctly.

Thanks!

Re: Any alternatives for empty expression in a Pivot table ?

Wed, 18 Sep 2013 08:15:50 GMT

Hi All,

Think this is the root cause. The Qlikview combine all the blank data into one row. Below is example to illustrate the example.

Screenshot of raw data:

Screenshot of chart table:

If you compare the data for questions 2 in raw data and chart table, the Qlikview combine my 2 rows of blank data together. This give the illusion of blank value. The question to this will change to: How to make the blank raw data into unique.

Chart table Qns 2 should have 3 rows instead of the current 2 rows.

Re: Any alternatives for empty expression in a Pivot table ?

Thu, 19 Sep 2013 09:19:52 GMT

Hi,

Any one can help on this ?

Thanks.

Re: Any alternatives for empty expression in a Pivot table ?

simondachstr — Thu, 19 Sep 2013 09:38:18 GMT

Can you please upload the above displayed Excel file?

Re: Re: Any alternatives for empty expression in a Pivot table ?

Mon, 23 Sep 2013 01:39:58 GMT

Hi Martin,

As requested.

Thanks!

Re: Any alternatives for empty expression in a Pivot table ?

simondachstr — Mon, 23 Sep 2013 09:56:07 GMT

Your issue is because of the nature of how you display your data and I can understand this can be tricky. QlikView combines all cells that are the same in a Pivot-Table.

An idea to create a work around is the following: Create an additional dimension called Question ID (QID). It will carry the numbers 1,2,3.. etc. After loading them into your QV application, pick QID as your dimension AFTER comments. This way it will become clear that your "empty" field exist twice.

What do you think?

Re: Re: Any alternatives for empty expression in a Pivot table ?

qlikpahadi07 — Mon, 23 Sep 2013 11:55:25 GMT

May be this will help you

create a Extra Column as Row

LOAD Subject_Name,

RowNo() as row,

Questions,

Comments

FROM table

Change Background Color to chart Background same for Expression.

Find the attachment