Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor III

Get the field that linl tothe max value

Hi,

I have variable in the UI that present the max value of deposit.

max(DepositAmount) - this value appears in the text box object.

I want to show in the text box not only the amount it self but also  the name of the customer.

In other words, I wondering if there is a function that returns fields that link to the max value like customer name/country and etc.

Hope it's clear what I asking 😕

Tags (3)
1 Solution

Accepted Solutions
Highlighted
Honored Contributor

Re: Get the field that linl tothe max value

If you have many Customer/Country which have the same DepositeAmount, use

concat({<DepositAmount={"$(=max(DepositAmount))"}>}Customer,'|') to get all  separated With a | sign, otherwise you will get a - (null value)

View solution in original post

7 Replies
Highlighted
Honored Contributor

Re: Get the field that linl tothe max value

Hi,

maybe there is better solutions, but I normally use setanalysis for this.

Try With only({<DepositAmount={"$(=max(DepositAmount))"}>}Customer)

Highlighted
Honored Contributor

Re: Get the field that linl tothe max value

If you have many Customer/Country which have the same DepositeAmount, use

concat({<DepositAmount={"$(=max(DepositAmount))"}>}Customer,'|') to get all  separated With a | sign, otherwise you will get a - (null value)

View solution in original post

Highlighted
Valued Contributor III

Re: Get the field that linl tothe max value

Also, I would add a predicate DISTINCT

=Concat(Distinct {<

  Sum = {"$(=max(Sum))"}

>}Customer, ', ')

In case you have duplicates like in 4th and 5th rows of the code:

LOAD * INLINE [

    Customer, Sum

    A, 1

    B, 4

    B, 4

    C, 3

    C, 4

];

Result would be only B, C

Highlighted
Honored Contributor

Re: Get the field that linl tothe max value

Yes thats correct if he use Customer from the fact table, but i'm pretty sure that he have a dimesion table which contain the distinct customer, but if I'm wrong Distinct predicate is needed.

Highlighted
Not applicable

Re: Get the field that linl tothe max value

Hi David,

As for my understanding

='Name:       '&concat({<DepositAmount={"$(=max(DepositAmount))"}>}customer,'|') & Chr(13)&            

'value:          '&Aggr(max(DepositAmount),customer)&

Highlighted
Not applicable

Re: Get the field that linl tothe max value

Hi

David can you try this one

=concat({<Amount={"$(=max(Amount))"}>}Name,'|') & ' ,'&max(Amount)

Highlighted
New Contributor III

Re: Get the field that linl tothe max value

Thanks it work