How to get the maximum Date for an item

[#1]

Dear All,

I have to retrieve values only for the maximum date for each item. HOw can I achieve the same.

DATA

ITEM     DATE                          AMOUNT

A              01-01-2012                    100

A             12-01-2012                     150

A             18-01-2012                     121

B              01-01-2012                    190

B             12-02-2012                     170

B          21-02-2012                        161

Output

ITEM               AMOUNT

A                     121

B                      161

Cheers

[swuehl · Accepted solution]

You can use FirstSortedValue():

Create a chart with dimension ITEM and as expression:

=FirstSortedValue(AMOUNT, -DATE)

where DATE needs to have a numerical representation, like all dates created or interpreted by QV.

[#3]

Hi,

I tried the above code but it doesn't giev a correct figure. I have attach an example.

Let me know where did i went wrong.

[swuehl]

I believe the expression is correct.

It seems that even you've used trim(ITEM) in your load, your ITEM field values are not correctly trimmed to the single characters. 'A' and 'A' are two distinct values (having a length of 34 characters). Maybe some hard spaces.

Try

trim(purgechar(ITEM, chr(160))) as ITEM

in your load.

Hope this helps,

Stefan

[#5]

HI,

thanks buddy..!! I will try it my scenario and keep you updated on the same.