topic Re: Problem with FirstSortedValue when dealing with repeated values in QlikView

Problem with FirstSortedValue when dealing with repeated values

Tue, 27 Aug 2013 06:55:09 GMT

Hi All,

I have a problem with FirstSortedValue. Please help.

temp:

load * inline

[

col1, col2, col3

1, a1, 1000

2,c1, 2000

3, b1, 3000

3,b1,4000

];

I have data as above. And in a text box I have =FirstSortedValue( col2, -col1). A simple expression. I want to get the value of col2 for the maximum value of column 1. As such with the data you see this expression will return null. If I remove the last line, it will return 'b1'. I know for a fact, the combination of col1 and col2 will always be unique. Do we have any work around please?

Thanks,

Ram

Re: Problem with FirstSortedValue when dealing with repeated values

ashwanin — Tue, 27 Aug 2013 07:01:00 GMT

Hi Ram,

In expression make Col3 as Decreasing i.e do shortening the values.

rgds

Ashwani

Re: Problem with FirstSortedValue when dealing with repeated values

Tue, 27 Aug 2013 07:03:54 GMT

Sorry I don't understand.

Re: Problem with FirstSortedValue when dealing with repeated values

whiteline — Tue, 27 Aug 2013 07:16:15 GMT

Hi.

There two ways: datamodel changing or complex expression.

If you're sure that these line are always the same - store it as one line in a separate table. So that you can use standart FirstSortedValue.

Or you can write a complex expression with aggr, Max and Only functions, using the same logic as FirstSortedValue. But this approach is not flexible when you need to add different dimensions in your charts.

Re: Problem with FirstSortedValue when dealing with repeated values

Tue, 27 Aug 2013 07:22:41 GMT

If I go down the separate table route, how do I avoid a link to that table. e.g. in my case if i create a table with just distinct values for col1 and col2 how do I avoid the table from getting linked to the existing tables?

Re: Problem with FirstSortedValue when dealing with repeated values

Tue, 27 Aug 2013 07:34:26 GMT

Hi,

firstsortedvalue ([ distinct ] expression [, sort-weight [, n ]])

By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return NULL.

Here Inline Table Contain

temp:load * inline[
col1, col2, col3
1, a1, 1000
2,c1, 2000

3, b1, 3000

3,b1,4000
];

If I use Firstsortvalue(col1,-col2) then this will gives blank value. As per function functionality.

If I remove one row. Then my inline function will be

temp:load * inline[
col1, col2, col3
1, a1, 1000
2,c1, 2000
3,b1,4000
];

Firstsortvalue(col1,-col2)

The out put will be b1 that is desired out put.

Regards,

Starwberry

Re: Problem with FirstSortedValue when dealing with repeated values

Tue, 27 Aug 2013 07:40:48 GMT

Yes... i believe this function is working like the same.

The answer is b1

Re: Problem with FirstSortedValue when dealing with repeated values

Tue, 27 Aug 2013 07:41:28 GMT

Thanks Mohit. Yes, I understand the problem absolutely. Looking for workarounds.

Re: Problem with FirstSortedValue when dealing with repeated values

Tue, 27 Aug 2013 07:43:42 GMT

If there is no workaround, I'll have to get the value of col2 of the latest record in my load script in a variable and use it. But I have kept it as the last resort.

Re: Problem with FirstSortedValue when dealing with repeated values

rajeshvaswani77 — Tue, 27 Aug 2013 07:46:16 GMT

Hi,

If there are duplicates then the firstsortedvalue will return a Null.

thanks,

Rajesh Vaswani

Re: Problem with FirstSortedValue when dealing with repeated values

whiteline — Tue, 27 Aug 2013 07:47:05 GMT

To separate table just give differ field names.

Why do you want to make it separate ? I thiught it should be connected with the other data through col2 as a key.

So in one table you have col1 and col2. In the other col2 and col3.

Re: Problem with FirstSortedValue when dealing with repeated values

jagan — Tue, 27 Aug 2013 08:16:58 GMT

Hi,

please find attached file for solution. Hope it helps you.

Regards,

Jagan.

Re: Problem with FirstSortedValue when dealing with repeated values

Wed, 28 Aug 2013 05:12:11 GMT

Thanks Jagan. That is perfect.

Re: Problem with FirstSortedValue when dealing with repeated values

Wed, 28 Aug 2013 05:21:56 GMT

However one question. Any means by which we can do this without the Unique ID surrogate key?

Re: Problem with FirstSortedValue when dealing with repeated values

whiteline — Thu, 29 Aug 2013 08:07:43 GMT

Easily:

=Only({<col1={$(=Max(total col1))}>} col2)