topic Re: How to write expression to get latest column values in QlikView

How to write expression to get latest column values

Tue, 10 Sep 2013 15:58:51 GMT

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.

Re: How to write expression to get latest column values

Gysbert_Wassenaar — Tue, 10 Sep 2013 16:05:40 GMT

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.

Re: How to write expression to get latest column values

Clever_Anjos — Tue, 10 Sep 2013 16:07:28 GMT

Use

FirstSortedValue(Comment,-datefield)

Re: How to write expression to get latest column values

Tue, 10 Sep 2013 16:10:01 GMT

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

Re: How to write expression to get latest column values

Carlos_Reyes — Tue, 10 Sep 2013 16:12:04 GMT

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.

Re: How to write expression to get latest column values

Clever_Anjos — Tue, 10 Sep 2013 16:12:12 GMT

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?

Re: How to write expression to get latest column values

Tue, 10 Sep 2013 16:25:11 GMT

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?

Re: How to write expression to get latest column values

Clever_Anjos — Tue, 10 Sep 2013 16:29:49 GMT

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

Re: How to write expression to get latest column values

Wed, 11 Sep 2013 10:16:16 GMT

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.

Re: How to write expression to get latest column values

Clever_Anjos — Wed, 11 Sep 2013 13:40:50 GMT

Yes, its possible Amelia,

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

Re: How to write expression to get latest column values

Thu, 12 Sep 2013 15:10:30 GMT

Hi Thanks very much it's working.

Amelia

Re: How to write expression to get latest column values

Fri, 13 Sep 2013 13:35:58 GMT

Hi,

If I am using FirstSortedValue(Comment,-ContactDate) am getting comments but forename,surname,jointname,staffname are showing blank values. could you please help me in this as it is urgent now please.

Re: How to write expression to get latest column values

Fri, 13 Sep 2013 14:18:14 GMT

Please help as I strucked here and couldn't able to get the names for several clients

Re: How to write expression to get latest column values

Clever_Anjos — Fri, 13 Sep 2013 14:26:43 GMT

Hello,

forename,surname,jointname,staffname fields are in same table?

Do you have skype? If you have, it would be easier to help, please add me: cleveranjos

Re: How to write expression to get latest column values

Fri, 13 Sep 2013 14:30:27 GMT

Hi,

Thanks for this.

forename,surname,jointname are from one table , staffname is from one table and the comment is from another table.

Could you help

As I am in office and don't have server access to login from my home so that's the reason I couldn't able to login skype from office

Thanks.

Re: How to write expression to get latest column values

Gysbert_Wassenaar — Fri, 13 Sep 2013 14:33:32 GMT

That sounds like there are several forenames/surnames/jointnames/staffnames with the same contactdate. In that case you can't use the firstsortedvalue. Perhaps you can concatenate them first:

firstsortedvalue( aggr(concat(forename, ', '),ConcactDate), -ContactDate)

Re: How to write expression to get latest column values

Clever_Anjos — Fri, 13 Sep 2013 14:38:12 GMT

Do you have any keys between those tables?

Re: How to write expression to get latest column values

Fri, 13 Sep 2013 15:02:33 GMT

Thanks and this is also not showing the names. please help and moreover when I make chages to Live file every time it is decreasing the Live qlikview file size. Is it recommended to reload and save manually apart from reload engine? Could you please let me know.

Re: How to write expression to get latest column values

Fri, 13 Sep 2013 15:03:27 GMT

Yes I have keys in names table and to join all these columns I used Link Table.

Thanks.

Re: How to write expression to get latest column values

Clever_Anjos — Fri, 13 Sep 2013 16:10:42 GMT

Is it possible to post a sample of your app?