topic Maxstring in Set Notation in QlikView

Maxstring in Set Notation

julesjohnson — Wed, 25 Nov 2020 16:16:04 GMT

Hello Everyone,

I am using the following statement to return the highest dollar amount of the ticket with a status of *open*, and a request type of *Incident* and it works just fine.

=Num(MaxString({$<[Request Type_S]={'*Incident*'},[Request Status_S]={'*open*'}>
} [CM_Cost/Hr]*[Elapsed_Time_S]),'$#,##0')

Now I want a statement that returns the corresponding “Request ID”. I have this statement so far but need to also include where the dollar amount is maxstring (CM_Cost/Hr]*[Elapsed_Time_S]). Is there a way I can do this?

=only({$ <[Request Type_S]={'*Incident*'},[Request Status_S]={'*open*'}>
} [Request ID_S])

Re: Maxstring in Set Notation

sunny_talwar — Mon, 11 Jun 2018 18:46:05 GMT

May be you need this

=FirstSortedValue({$<[Request Type_S] = {'*Incident*'}, [Request Status_S] = {'*open*'}>} [Request ID_S], -[CM_Cost/Hr] * [Elapsed_Time_S])

Re: Maxstring in Set Notation

julesjohnson — Mon, 11 Jun 2018 20:22:09 GMT

Wow, that worked!!

Thanks Sunny