Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Return Last Sting In Field

Hi All,

I have this set of data

Claimant TypeClaim NumberVer NoPayment ReservesRecovery ReservesPaymentsRI RecoveriesNon RI Recoveries
ADRS1002021925.000.000.000.000.00
ADRS1002022925.000.000.000.000.00
ADRS1002023925.000.000.000.000.00
PIRS10020214585.000.000.000.000.00
PIRS10020224585.000.000.000.000.00
PIRS10020234585.000.000.000.000.00
TPRS10020211000.000.000.000.000.00
TPRS10020221000.000.000.000.000.00
TPRS10020231000.000.000.000.000.00


I wish to return the maximum Ver No and then the corresponding data with that row ( See Below ). I have used the MAX function in an expression to select the maximum Ver no. What expression can i use to return the string which corresponds with the rest of the data on the Max Ver No field?

Claimant TypeClaim NumberVer NoPayment ReservesRecovery ReservesPaymentsRI RecoveriesNon RI Recoveries
TPRS10020231000.000.000.000.000.00
PIRS10020234585.000.000.000.000.00
ADRS1002023925.000.000.000.000.00


Thanks in Advance

Regards
James

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

it depends on the number of dimensions. See QV Help for more information about AGGR.

Getting the right lines during data reload is the easiest way and response time for end users are low.

Rainer

View solution in original post

21 Replies
Miguel_Angel_Baeyens

Hello James,

Create a straight table, select Claimant Type and Claim Number as dimensions and

Max([Ver No])


As expression.

Then Sum([Payment Reserves]) and so as the rest of the expressions.

Hope that helps.

Miguel_Angel_Baeyens

Hi,

Another option is to use set analysis with "Ver No" as dimension as well:

Sum({< [Ver No] = {$(=Max([Ver No]))} >} [Payment Reserves])


Ading the bold part in all expressions.

Hope that helps.

Not applicable
Author

that didnt work.

note i am aggregating all of this data already. Each of the cost columns is aggreggatedfrom several other data lines.

will that make any difference???

Miguel_Angel_Baeyens

Hello,

Obviously it does make some differences, since that works depending on your datamodel and expressions.

Can you share how are you getting now those expressions so we can check your code?

Regards.

IAMDV
Luminary Alumni
Luminary Alumni

Miguel - I have similar question and I had already searched on the forum without any luck. Lets say we have table...

Product Sales

A 10

B 12

C 15

If I wanted to refer to the product with maximum sales i,e. 'C' in this example by using an expression (with and without set analysis). This is equivalent to VLookup on Max(Sales) in Excel.

Please can you help?

TIA!

Cheers - DV

Not applicable
Author

Hi

i have attched an example dataset.

Thanks for your help Miguel Smile

Miguel_Angel_Baeyens

Hello,

This seems to work (returns -8000) on the left table:

sum({< [Ver No] = {$(=Max([Ver No]))} >} Incurred)


On the right table you get two records, one for -3000 and the other -5000

Hope that helps

Not applicable
Author

That works great Miguel...

thank you. Although the TP returns no value ?? Why is this??

could i ammend the set analysis to make it return a value ??

Thanks Again

IAMDV
Luminary Alumni
Luminary Alumni

Miguel - Please can you also look into my question?

Thanks in ancitipation.

Cheers - DV