topic Re: FirstSortedValue Help!! in QlikView

FirstSortedValue Help!!

sujeetsingh — Fri, 10 Oct 2014 13:29:00 GMT

Hello,

I am stuck up with an expression in straight table:

I have data as below

ID, Date ,Status1 ,Status2

1 , 2/10/2011, A , B

1, 3/11/3011 , B , G

1, 4/11/2012,L ,L

1, 5/12/2012 ,A ,H

I am using expression as

aggr(FirstSortedValue([Status1], -[RowNo()]), ID) but gives value L but it should give the latest value A with latest date '5/12/2012'

Thanks

Re: FirstSortedValue Help!!

anbu1984 — Fri, 10 Oct 2014 13:37:24 GMT

=aggr(FirstSortedValue([Status1], -Date), ID)

Re: FirstSortedValue Help!!

Peter_Cammaert — Fri, 10 Oct 2014 13:39:35 GMT

Why don't you use the value of [Date] as second parameter to FirstSortedValue, instead of RowNo()? IMHO RowNo() will in this case give unpredictable results.

Peter

Re: FirstSortedValue Help!!

Fri, 10 Oct 2014 13:39:45 GMT

Hi,

Try creating the RowNo inside the script. and then using it in the expression.

Regards

Re: FirstSortedValue Help!!

sujeetsingh — Fri, 10 Oct 2014 13:50:51 GMT

Do not works

Re: FirstSortedValue Help!!

sujeetsingh — Fri, 10 Oct 2014 13:57:02 GMT

sorry can not do this in the script

Re: FirstSortedValue Help!!

Fri, 10 Oct 2014 14:07:24 GMT

Then try,

FirstSortedValue(Status1,aggr(max(Date),ID),1)

Re: FirstSortedValue Help!!

sujeetsingh — Fri, 10 Oct 2014 14:08:56 GMT

Leave the expressions ....just what i want is

Straight table with ID as dimension

I want Status1 with latest date aggregated on per ID

Re: FirstSortedValue Help!!

sujeetsingh — Fri, 10 Oct 2014 14:13:17 GMT

This works ,

But when there is Status1 as null then again a value with latest date then it is taking the one with null value for Status1.

Re: FirstSortedValue Help!!

Peter_Cammaert — Fri, 10 Oct 2014 14:13:49 GMT

Put ID as dimension in your table, and =max(Date) as expression.

Note that in all cases, including this one, Date has to exist as a Field, not just a column label in your table.

Re: FirstSortedValue Help!!

JonnyPoole — Fri, 10 Oct 2014 14:17:57 GMT

It seems to accept the distinct even the expression editor flags it. This will give you 'A'

FirstSortedValue( distinct Total <ID> Status1, -Date)

Re: FirstSortedValue Help!!

sujeetsingh — Fri, 10 Oct 2014 14:21:17 GMT

I want status value with the max date().

There is grouping at the table based in ID.

Re: FirstSortedValue Help!!

Fri, 10 Oct 2014 14:28:16 GMT

Try the below,

FirstSortedValue(Status1,aggr(max({<Status1={'*'}-{''}>}Date),ID),1)

Re: Re: FirstSortedValue Help!!

Peter_Cammaert — Fri, 10 Oct 2014 14:29:22 GMT

OK, then FirstSortedValue will still be your hero.

See doc below.

OK?

Peter

Re: Re: FirstSortedValue Help!!

Peter_Cammaert — Fri, 10 Oct 2014 14:31:11 GMT

BTW dates in the year 3011 are still far away. But in the example I copied them anyway in sets for IDs 5 and 9

Re: FirstSortedValue Help!!

sujeetsingh — Sat, 11 Oct 2014 06:19:17 GMT

Peter thanks, for you all reply.

Actually i have Dimension as First name , Last name which is one to one related to ID.

In that case the expressions gives some wrong data.

Re: FirstSortedValue Help!!

JonnyPoole — Sat, 11 Oct 2014 17:26:19 GMT

curious... any luck with this expression ?

Re: FirstSortedValue Help!!

sujeetsingh — Tue, 14 Oct 2014 04:09:56 GMT

Here is the answer

Re: Expression Help