topic Re: How Do I supress Expression in a pivot table in QlikView

How Do I supress Expression in a pivot table

helen_pip — Tue, 02 Oct 2012 11:01:14 GMT

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

Re: How Do I supress Expression in a pivot table

whiteline — Tue, 02 Oct 2012 12:16:09 GMT

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.

Re: How Do I supress Expression in a pivot table

helen_pip — Tue, 02 Oct 2012 12:30:33 GMT

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

Re: How Do I supress Expression in a pivot table

whiteline — Tue, 02 Oct 2012 13:50:51 GMT

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.

Re: How Do I supress Expression in a pivot table

helen_pip — Tue, 02 Oct 2012 14:31:29 GMT

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 Seen	Total Attends		6

You have been really helpful

Thanks

Helen

Re: How Do I supress Expression in a pivot table

whiteline — Tue, 02 Oct 2012 14:38:22 GMT

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

2) Change the order Expresion<->Level2

Re: How Do I supress Expression in a pivot table

helen_pip — Tue, 02 Oct 2012 14:52:57 GMT

Thank you! This is just what I need to achieve