topic Return Last Sting In Field in QlikView

Return Last Sting In Field

Thu, 31 Mar 2011 08:29:59 GMT

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

Return Last Sting In Field

Miguel_Angel_Baeyens — Thu, 31 Mar 2011 09:08:33 GMT

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

Miguel_Angel_Baeyens — Thu, 31 Mar 2011 09:12:13 GMT

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

Thu, 31 Mar 2011 09:24:21 GMT

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

Miguel_Angel_Baeyens — Thu, 31 Mar 2011 09:33:45 GMT

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

IAMDV — Thu, 31 Mar 2011 09:34:33 GMT

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

Thu, 31 Mar 2011 09:39:54 GMT

i have attched an example dataset.

Thanks for your help Miguel

Return Last Sting In Field

Miguel_Angel_Baeyens — Thu, 31 Mar 2011 09:48:38 GMT

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

Thu, 31 Mar 2011 09:58:31 GMT

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

IAMDV — Thu, 31 Mar 2011 10:01:02 GMT

Miguel - Please can you also look into my question?

Thanks in ancitipation.

Cheers - DV

Return Last Sting In Field

Miguel_Angel_Baeyens — Thu, 31 Mar 2011 10:58:22 GMT

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

Thu, 31 Mar 2011 11:28:25 GMT

Hi Miguel

your solution only returns the reults for the AD & PI rows. i need for every row.

See attached file

Return Last Sting In Field

Thu, 31 Mar 2011 11:45:45 GMT

Hi,

see the attached example.

Good luck!

Rainer

Return Last Sting In Field

Thu, 31 Mar 2011 12:15:54 GMT

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

Thu, 31 Mar 2011 12:33:31 GMT

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

Thu, 31 Mar 2011 13:00:31 GMT

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

Return Last Sting In Field

Miguel_Angel_Baeyens — Thu, 31 Mar 2011 13:13:02 GMT

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

Thu, 31 Mar 2011 13:41:40 GMT

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

Miguel_Angel_Baeyens — Thu, 31 Mar 2011 13:50:22 GMT

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

Wed, 13 Apr 2011 11:16:13 GMT

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