topic Re: FirstSortedValue in QlikView

FirstSortedValue

Anonymous — Wed, 28 Dec 2016 13:56:57 GMT

Hello All,

My requirement is to get only the records highlighted in yellow with the bold font as in the image attached.

Essentially if a record is amended the its ID get updated i.e ; followed by the number of times.So in such instances I only want the most recent update. thus the expression ;

=FIRSTSORTEDVALUE(AGGR(SUM(DISTINCT AMOUNT),CLIENT,ID)
,
-AGGR(SUM([AMENDMENT NUMBER]),CLIENT,ID)) where Amendment number is derived as MID(ID,14,3)) AS [AMENDMENT NUMBER].

But unfortunately this fails to work for client's with no amendment like Moses, Victor just a few.

Please help, Thanks

Re: FirstSortedValue

sunny_talwar — Wed, 28 Dec 2016 14:02:30 GMT

Try this:

=FirstSortedValue(Aggr(Sum(DISTINCT AMOUNT) ,CLIENT), -Aggr([AMENDMENT NUMBER], CLIENT))

Re: FirstSortedValue

swuehl — Wed, 28 Dec 2016 14:05:33 GMT

Try

Subfield(ID &';1' , ';' ,2) AS [AMENDMENT NUMBER]

Re: FirstSortedValue

sunny_talwar — Wed, 28 Dec 2016 14:09:51 GMT

This is very smart Stefan . I have not tested this, but this might work also?

Alt(Mid(ID,14,3)), 1) as [AMENDMENT NUMBER]

Re: FirstSortedValue

Anonymous — Wed, 28 Dec 2016 14:40:07 GMT

Thanks Guys for the quick response, I also learnt something new.

However a client like Adeliade should show 3 records with a total amount of 450,252.00 and not 200,128.00

ADELIADE	TF1634818035	200,128.00	1,603.93	1	09/01/2017
ADELIADE	TF1634890173	50,068.00	214.50	1	09/01/2017
ADELIADE	TF1634898830	200,056.00	1,603.55	1	09/01/2017

Re: FirstSortedValue

sunny_talwar — Wed, 28 Dec 2016 14:43:17 GMT

May be you need a new ID field which is just like this:

LOAD Left(ID, 12) as New_ID

and then this:

=FirstSortedValue(Aggr(Sum(DISTINCT AMOUNT), CLIENT, New_ID), -Aggr([AMENDMENT NUMBER], CLIENT, New_ID))

Re: FirstSortedValue

Anonymous — Wed, 28 Dec 2016 15:50:31 GMT

Hi Sunny,

Tried that but but did not avail....

Re: FirstSortedValue

sunny_talwar — Wed, 28 Dec 2016 15:51:11 GMT

Would you be able to share your qvw or a sample?

Re: FirstSortedValue

Anonymous — Wed, 28 Dec 2016 16:01:42 GMT

Your email please?

Re: FirstSortedValue

sunny_talwar — Wed, 28 Dec 2016 16:06:13 GMT

I have added you as a connection and you should be able to send me a private message (PM). I can share my id on PM.

Re: FirstSortedValue

Anonymous — Wed, 28 Dec 2016 16:13:44 GMT

As request please find attached.

Re: FirstSortedValue

sunny_talwar — Wed, 28 Dec 2016 16:25:42 GMT

This seems like the data you have in the chart, I would rather look at the aggregated data or the your qvw application

Re: FirstSortedValue

Anonymous — Wed, 28 Dec 2016 17:16:28 GMT

Sunny, PFA.

Re: FirstSortedValue

sunny_talwar — Wed, 28 Dec 2016 17:28:15 GMT

This may be?

=FirstSortedValue(Aggr(SUM(DISTINCT AMOUNT),iCustomer_ID, NEW_ID, [AMENDMENT NUMBER]), -Aggr([AMENDMENT NUMBER], iCustomer_ID, NEW_ID, [AMENDMENT NUMBER]))