Determining or accessing the Straight Table column Max expression?

[richardouellett]

Hi,

I am using a Qlikview Straight Table applying a calculated dimension filter below and then suppressing rows when value is null in the dimensions tab. (2nd image below))

=if(if(SS='x'and PrePrioity ='ST' and i1_4>1 and (Location='ED' or Location='ED Hold' ) and Code<>'4066000' and Code<>'423656170' and Code<>'422533328' and Code<>'554865740' and Code<>'554878353' and not TstName LIKE 'U*',1,0)=1,Sequence) 

I have calculated the Max values for columns Step 1 – Step 3 & Duration on the 1^st row.  I now want to access the Max Value in Step 1 (Image below) to use in a new formula but am unable to determine the formula QlikView used to derive the value of 2 below. Can you help Me obtain the formula?

Message was edited by: Richard Ouellette I cannot share the app but did attach a mock up Presidents.qvs with the max [Served From] field.

Message was edited by: Richard Ouellette PresidentsII.qvw added 1/31

[sunny_talwar]

Would you be able to share your application for us to see what you have?

[richardouellett]

[sunny_talwar]

In the example you have used, the if statement is relatively simple, so this should work for you. But original app have a complex if statement.... but you get the idea

=Max({<[Served From] = {'19*'}>}Aggr([Served From], President, Party))

[richardouellett]

Sunny

This was very helpful and I am still trying to get my arms around it and as a result uploaded another example where the selection criteria is limited to the dimension fields only and not the expression fields I am trying to calculate the Max. Few observations:

1. I cannot share the actual application but please note that the query is very long and complex and this needs to be taken into account with the solution. As a result I am thinking that it can be written into the script, e.g., SET vQuery = ....; to simplify the solution.
2. The straight table results of 147 exceptions is derived from the query below from a database of 1 million rows plus.  The derived Max for expressions (Step 1 - 3 & Duration) need to be limited to the 147 exceptions in the straight table.

=if(if(SS='x'and PrePrioity ='ST' and i1_4>1 and (Location='ED' or Location='ED Hold' ) and Code<>'4066000' and Code<>'423656170' and Code<>'422533328' and Code<>'554865740' and Code<>'554878353' and not TstName LIKE 'U*',1,0)=1,Sequence)

Hopefully this helps clarify my need a bit more.  Feedback welcome.  Below is an image from the uploaded PresidentsII.qvs

Rick

[sunny_talwar · Accepted solution]

This:

=Max({<Party = {'Democrat'}, President = {"=Len(President) > 15"}>}Aggr([Served From], President, Party))

I think what ever your id statement is doing should be easy to replicate in set analysis

Max(/SetAnalysis\ Aggr(/Expression\, /Dimensions\))

Set Analysis

{<SS = {'x'}, PrePrioity = {'ST'}, i1_4 = {'>1'}, Location = {'ED', 'ED Hold'}, Code -= {4066000, 423656170, 422533328, 554865740, 554878353}, TstName -= {'U*'}>}

Expression and dimensions should already be available to you..

HTH

Best,

Sunny

[richardouellett]

Sunny

Thank you for you valuable help!

Best

Rick

[sunny_talwar]

Not a problem. I am glad I was able to help.

Best,

Sunny