How to write expression to get latest column values

[#1]

Hi Friends,

I have a qlikview file which contains pivot and straight chart items. In one straight Chart I need to get Latest Comment which is getting the latest comments from access database table. But I am not getting the latest comment. suppose if the user added the latest comment today I need to get that in the column value.

I used the below expression to get the latest comment

=MaxString(Comment)

but it's not working as I am getting old comments which are added a few days back.

Could anyone help me in this how to use expression to get the latest comment.

Thanks.

[Gysbert_Wassenaar]

First of all you will need a field that will have a unique value for each comment and increases in value for each comment added. A timestamp field is one option, a counter field would do too. Once you have such a field you can use the firstsortedvalue function to get the latest comment: firstsortedvalue(CommentField, -TimestampField)

See this video for an introduction to the firstsortedvalue function.

[Clever_Anjos]

Use

FirstSortedValue(Comment,-datefield)

[#4]

Thanks for this. But which datefield I need to use? could you tell me.

[Carlos_Reyes]

Well... I don't know if this is the best option but right off the bat I would convert your comments field into a dual field, so for each comment I would add a numeric value, something like:

LOAD  Dual (CommentField, CommentDate) as Comment

So, if given your table dimensions you may get more than one comment per row, when you use the MaxString function you'll only get the comment value with the biggest CommentDate value. If it's possible to have more than one comment per day, make sure that CommentDate has the time detail so it's different for each Comment.

[Clever_Anjos]

Look at your Comments Table,is there any date field there that informs the date?
Or any other field that tells the order in which they were posted?

[#7]

Yes I have two date fields which are used in expressions tab

one is  Date(ContactDate,'DD/MM/YYYY')   as ContactDate  from one table

another one is Date(AppointmentDate,'DD/MM/YYYY')   as  AppointmentDate  from another table.

So, in this case how can I use? ios there any proablem with Date function?

[Clever_Anjos · Accepted solution]

Amelia,

which field describes when the Comment was posted? I´m gessing it´s ContactDate, so your expression would be

FirstSortedValue(Comment,-ContactDate).

Please note the "-" sign so we want the "Last Value" and not first one

[#9]

Thanks for your answer. Yes it is correct I used ContactDate. Actually I used Conatctdate like below

Date(ContactDate,'DD/MM/YYY') as ContactDate

If I use ContactDate I am getting for eg: 09/09/2013 11:09am. to aviod this I used date function. But I need the column as it is without using date function. Could you let me know whether it is possible or not.

Thanks.

[Clever_Anjos]

Yes, its possible Amelia,

Try FirstSortedValue(Comment,-ContactDate) and post what happens