Return Last Sting In Field

[#1]

Hi All,

I have this set of data

Claimant Type	Claim Number	Ver No	Payment Reserves	Recovery Reserves	Payments	RI Recoveries	Non RI Recoveries
AD	RS100202	1	925.00	0.00	0.00	0.00	0.00
AD	RS100202	2	925.00	0.00	0.00	0.00	0.00
AD	RS100202	3	925.00	0.00	0.00	0.00	0.00
PI	RS100202	1	4585.00	0.00	0.00	0.00	0.00
PI	RS100202	2	4585.00	0.00	0.00	0.00	0.00
PI	RS100202	3	4585.00	0.00	0.00	0.00	0.00
TP	RS100202	1	1000.00	0.00	0.00	0.00	0.00
TP	RS100202	2	1000.00	0.00	0.00	0.00	0.00
TP	RS100202	3	1000.00	0.00	0.00	0.00	0.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 Type	Claim Number	Ver No	Payment Reserves	Recovery Reserves	Payments	RI Recoveries	Non RI Recoveries
TP	RS100202	3	1000.00	0.00	0.00	0.00	0.00
PI	RS100202	3	4585.00	0.00	0.00	0.00	0.00
AD	RS100202	3	925.00	0.00	0.00	0.00	0.00

Thanks in Advance

Regards
James

[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.

[#4]

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]

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

[#7]

Hi

i have attched an example dataset.

Thanks for your help Miguel

[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

[#9]

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]

Miguel - Please can you also look into my question?

Thanks in ancitipation.

Cheers - DV