21 Replies Latest reply: Apr 13, 2011 8:38 AM by Deepak Vadithala

# Return Last Sting In Field

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

Regards
James

• ###### Return Last Sting In Field

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.

• ###### Return Last Sting In Field

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.

• ###### Return Last Sting In Field

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

• ###### Return Last Sting In Field

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.

• ###### Return Last Sting In Field

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

• ###### Return Last Sting In Field

Hello,

If you only want to show the product with the maximum sales, try Product as dimension and this as expression

`Only({< Sales = {\$(=Max(Sales))} >} Sales)`

Hope that helps

• ###### Return Last Sting In Field

Hi

i have attched an example dataset.

Thanks for your help Miguel

• ###### Return Last Sting In Field

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

• ###### Return Last Sting In Field

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

• ###### Return Last Sting In Field

Miguel - Please can you also look into my question?

Thanks in ancitipation.

Cheers - DV

• ###### Return Last Sting In Field

Hi,

see the attached example.

Good luck!

Rainer

• ###### Return Last Sting In Field

I love the solution!!!

but why doesnt it work when i do it as an expression???

I appreciate your help

• ###### Return Last Sting In Field

I love the solution!!!

but why doesnt it work when i do it as an expression? Where is the result for PI ??

I appreciate your help

• ###### Return Last Sting In Field

Hello,

FirstSortedValue() returns one value, so does Max(), and it may require as much aggregations as fields you need to calculate. As you want to get the sum (aggregation) of values in Incurred when there may one or more records with the Max() value, so the following should work in the expression

`Sum(If(Aggr(NODISTINCT Max([Claim Transaction Sequence Number]), [Claimant Type]) = [Claim Transaction Sequence Number], Incurred))`

Hope this helps.

• ###### Return Last Sting In Field

Thank you Miguel

This seems to work. But only when there is one claim number. as soon as this expression is allocaed to a dataset with more than one claim number it stops working....

This is my fault as i didnt specify in my request or on the example. .

Regards

James

• ###### Return Last Sting In Field

Hello James,

Actually this expression is another option to what Rainer accurately suggests, meaning that depends on your datamodel and your chart the number of aggregations you have to do. Adding the "Claim Number" to the aggregation, the formula works:

`Sum(If(Aggr(NODISTINCT Max([Claim Transaction Sequence Number]), [Claimant Type], [Claim Number]) = [Claim Transaction Sequence Number], Incurred))`

Hope that helps.

• ###### Return Last Sting In Field

Hi

I am in a similar position to before. the difference this time is that i want to return the last text string instead of the last numerical value.

how could this be changed to return the last text string??

`Sum(If(Aggr(NODISTINCT Max([Claim Transaction Sequence Number]), [Claimant Type], [Claim Number]) = [Claim Transaction Sequence Number], Incurred))`

Many thanks James

• ###### Return Last Sting In Field

Hello James,

Try with MaxString() function instead of Max() function. That should work.

Regards.

• ###### Return Last Sting In Field

Thanks Miguel - This is very helpful!

• ###### Return Last Sting In Field

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