Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Tags (1)
1 Solution

Accepted Solutions
Not applicable

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

21 Replies
MVP
MVP

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.

MVP
MVP

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.

Not applicable

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

MVP
MVP

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.

IAMDV
Honored Contributor II

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

Not applicable

Return Last Sting In Field

Hi

i have attched an example dataset.

Thanks for your help Miguel Smile

MVP
MVP

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

Not applicable

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

IAMDV
Honored Contributor II

Return Last Sting In Field

Miguel - Please can you also look into my question?

Thanks in ancitipation.

Cheers - DV

Community Browser