Solved: Any alternatives for empty expression in a Pivot t... - Qlik Community

Report Inappropriate Content · ‎2013-09-11

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!

qlikpahadi07 · ‎2013-09-23

Check my Solution attached :

I think i understood your problem correctly it's working with Data so must b with Blanks .

View solution in original post

marcus_sommer · ‎2013-09-12

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

- Marcus

Anonymous · ‎2013-09-12

Hi,

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

nizamsha · ‎2013-09-12

in presentation tab un check the show partical sum

Report Inappropriate Content · ‎2013-09-13

Hi,

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

Thanks!

Report Inappropriate Content · ‎2013-09-15

Hi Marcus,

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

Thanks!

Report Inappropriate Content · ‎2013-09-15

Hi Wassenaar,

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

Thanks!

Report Inappropriate Content · ‎2013-09-15

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.

marcus_sommer · ‎2013-09-16

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

Report Inappropriate Content · ‎2013-09-16

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

= ' '

Any alternatives for empty expression in a Pivot table ?