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.
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
1) [Reportable Month] << its your field, just add it as dimension to the end.
2) Change the order Expresion<->Level2
Synthetic pivot.png 8.1 K