Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

helen_pip
Contributor II

How Do I supress Expression in a pivot table

Hello,

I was wondering if you could kindly help me, hopefully with the syntax in my dimensions and expressions

How Do I supress Median, Fractile and Longest Wait under the "Left without being seen" section of my attached pivot table?

Basically how do I suppress the Nulls

I only want to display "Total Attends" in the "Left without being seen" section

Kind Regards

Helen

1 Solution

Accepted Solutions
whiteline
Honored Contributor II

Re: How Do I supress Expression in a pivot table

Hi.

There is plenty of ways to do it. I used the easiest to implement, just to show the idea.

Add this load to your script (a table structure and sort order):

LOAD * INLINE [

Level1, Level2, Expression,

Total Treatment Time, Admitted, Median

Total Treatment Time, Admitted, Fractile

Total Treatment Time, Admitted, Longest Wait

Total Treatment Time, Non Admitted, Median

Total Treatment Time, Non Admitted, Fractile

Total Treatment Time, Non Admitted, Longest Wait

To Initial Treatment,     , Median

To Initial Treatment,     , Fractile

To Initial Treatment,     , Longest Wait

Left Without Being Seen, , Total Attends

];

Then create a pivot table whith dimensions: Level1, Level2, Expression, [Reportable Month].

Add this expression (I just trunsformed yours):

=Pick(Match(Expression, 'Median', 'Fractile', 'Longest Wait', 'Total Attends')+1, 'NA', Time(Median([Total Wait])/1440, 'hh:mm'), Time(Fractile([Total Wait],0.95)/1440, 'hh:mm'), Time(Max([Total Wait])/1440, 'hh:mm'), Count([Total Wait]))

There is a match() part that calculates the appropriate index by 'Expression' dimension and pick() part that picks the cooresponding expression.

As there is only one expression, the text formats should be handled explicitly, thats why I added Time() function.

6 Replies
whiteline
Honored Contributor II

Re: How Do I supress Expression in a pivot table

Hi.

I think, you can't do it that way.

I suggest to transform your column with expressions so thet it will be a dimension.

Create your table structure instead of using ValueList().

Then use just appropriate complex expression.

helen_pip
Contributor II

Re: How Do I supress Expression in a pivot table

Hello

I know this is a big ask, but I have been trying to do this in my script, but resorted to ValueLists instead

With the example I have provided, are you able to provide me with more assistance on this?

Even if a little bit of help is all you can do

Thanks

Helen

whiteline
Honored Contributor II

Re: How Do I supress Expression in a pivot table

Hi.

There is plenty of ways to do it. I used the easiest to implement, just to show the idea.

Add this load to your script (a table structure and sort order):

LOAD * INLINE [

Level1, Level2, Expression,

Total Treatment Time, Admitted, Median

Total Treatment Time, Admitted, Fractile

Total Treatment Time, Admitted, Longest Wait

Total Treatment Time, Non Admitted, Median

Total Treatment Time, Non Admitted, Fractile

Total Treatment Time, Non Admitted, Longest Wait

To Initial Treatment,     , Median

To Initial Treatment,     , Fractile

To Initial Treatment,     , Longest Wait

Left Without Being Seen, , Total Attends

];

Then create a pivot table whith dimensions: Level1, Level2, Expression, [Reportable Month].

Add this expression (I just trunsformed yours):

=Pick(Match(Expression, 'Median', 'Fractile', 'Longest Wait', 'Total Attends')+1, 'NA', Time(Median([Total Wait])/1440, 'hh:mm'), Time(Fractile([Total Wait],0.95)/1440, 'hh:mm'), Time(Max([Total Wait])/1440, 'hh:mm'), Count([Total Wait]))

There is a match() part that calculates the appropriate index by 'Expression' dimension and pick() part that picks the cooresponding expression.

As there is only one expression, the text formats should be handled explicitly, thats why I added Time() function.

helen_pip
Contributor II

Re: How Do I supress Expression in a pivot table

Wow!

Thank you so much.....I would of been here years, months and days and would not of achieved that at this stage in my Qlikview devleopment

Could I push my luck and ask whether you can help me with the final stage

-I have entered the inline statement

-Created a pivot table as you have suggested, but I am not sure where to pick the [Reportable Month] from for my dimension?

-Also, my pivot table only comes out with 1 line of data when I copy the expression in

Level1   Expression   Level2 Data 
Left Without Being SeenTotal Attends6

You have been really helpful

Thanks

Helen

whiteline
Honored Contributor II

Re: How Do I supress Expression in a pivot table

1) [Reportable Month] << its your field, just add it as dimension to the end.

2) Change the order Expresion<->Level2

helen_pip
Contributor II

Re: How Do I supress Expression in a pivot table

Thank you! This is just what I need to achieve

Community Browser