7 Replies Latest reply: Jan 31, 2017 8:12 PM by Sunny Talwar

# Determining or accessing the Straight Table column Max expression?

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 1st 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

• ###### Re: Determining or accessing the Straight Table column Max expression?

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

• ###### Re: Determining or accessing the Straight Table column Max expression?

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))

• ###### Re: Determining or accessing the Straight Table column Max expression?

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

• ###### Re: Determining or accessing the Straight Table column Max expression?

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

• ###### Re: Determining or accessing the Straight Table column Max expression?

Sunny

Thank you for you valuable help!

Best

Rick

• ###### Re: Determining or accessing the Straight Table column Max expression?

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

Best,

Sunny