topic Re: FirstSortedValue in QlikView

FirstSortedValue

Thu, 24 Nov 2011 10:35:12 GMT

Hi All,

Please see attached table.

I have a table with the data in Columns A - D, and I want to return the values in Columns E and F.

I have tried using FirstSortedValue, which seems logically to be the function I should use, but I am not getting any returns.

Column E shows the minimum YearPeriod value in Column A for the Group in Column C.

In fact, all returns are "201001" because that is the minimum YearPeriod value for each Group.

Likewise, Column F shows the minimum YearPeriod value in Column A for the Type in Column D.

This time, there are some returns that are not "201001" (coloured) because there is no such YearPeriod value for these types.

In QlikView-speak, I have unsuccessfully used the following:

FirstSortedValue(TOTAL YearPeriod, Group)

...and, of course Type in place of Group for the second case.

Yet again I am doing something wrong... yet again I throw myself at the feet of the all powerful community.

Help Please!

8-)

Dave

QlikView for Windows Version 9.00.7646.9 SR6

Message was edited by: davewright - typo

swuehl — Thu, 24 Nov 2011 11:01:20 GMT

Hi Dave,

unfortunately, I can't see your attached table, could you check this please?

Please note that the Help states about FirstSortedValue:

"...If more than one value of expression share the same lowest sort-order, the function will return null...."

Could this be a problem with your data?

Regards,

Stefan

Thu, 24 Nov 2011 11:07:09 GMT

What an idiot!!!

THIS time I will attach the file.

I suspect, Stefan, that you are right, but I'll let you have a look.

Thanks.

Dave

swuehl — Thu, 24 Nov 2011 11:21:56 GMT

try

=min(total<Group> YearPeriod)

resp.

=min(total<Type> YearPeriod)

as expressions.

Regards,

Stefan

Thu, 24 Nov 2011 11:28:59 GMT

Of course...