topic Re: First Sorted Value Expression Prob in QlikView

First Sorted Value Expression Prob

nikhilgarg — Mon, 16 Mar 2015 13:35:48 GMT

Hey,

I am using an expression:

=$(=FirstSortedValue('[' & $(vDim) & ']' , _DimPosition , 1))

where vDim = subfield(_Dim , ':' , -1) and _DimPosition has numerical values from 1 to 28.

With ENable condition true with expression:

Getselectedcount(_Dim) >= 1 ;

Now , i dont understand what does BOTH above expression means and what does they returns ?

THanks

Re: First Sorted Value Expression Prob

rubenmarin — Mon, 16 Mar 2015 13:56:10 GMT

Hi Nikhil:

FirstSortedValue() --> Funtion to return a value sorted by another field (or by load order)

'[' & $(vDim) & ']' --> This is the field you want the value

_DimPosition --> This field gives the sort order for the first field

1 --> Tells to get the first sorted value

So it will return the first value of '[' & $(vDim) & ']' sorted by _DimPosition.

Re: First Sorted Value Expression Prob

nikhilgarg — Mon, 16 Mar 2015 14:21:51 GMT

Hey thanx but i have the same expressiona nd enable condition expression for 25 dimensions. Except the fact that for every consecutive expression numeric value increases from 1 o 25 i.e for second dimension expr is:

=$(=FirstSortedValue('[' & $(vDim) & ']' , _DimPosition , 1)) and

ENable condition true with expression:

Getselectedcount(_Dim) >= 2 ;

I dono why this 25 times written . I ahve 28 dimensions as total. Can you explain.

Thanks

Re: First Sorted Value Expression Prob

rubenmarin — Mon, 16 Mar 2015 14:30:53 GMT

Wow 25 dimensions, I don't know if it's beacuse my limited english but i'm hardly imagine that table, I suposse you can't upload a sample, isn't it? will be nice to better understanding.

To give a guess, maybe the developer doesn't think more than 25 will be selected or some of them are mutual exclusive.

This is doing something like, if there is 1 _Dim valueselected, it shows only the first expression. When you select 2 _Dim values, the first two expressions are enabled, and so on...

Re: First Sorted Value Expression Prob

awhitfield — Mon, 16 Mar 2015 14:31:08 GMT

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

Returns the first value of expression sorted by corresponding sort-weight when expression is iterated over a

number of records as defined by a group by clause. Sort-weight should return a numeric value where the lowest

value will render the corresponding value of expression to be sorted first. 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. By stating an n larger than 1, the nth

value in order will be returned. If the word distinct occurs before the expression, all duplicates will be disregarded.

Example:

Load Customer,

QlikView 11.20 SR8 301

25 Script Expressions

firstsortedvalue(PurchasedArticle, OrderDate) as FirstProductBought

from abc.csv

group by Customer;

Re: First Sorted Value Expression Prob

nikhilgarg — Tue, 17 Mar 2015 09:02:25 GMT

Hey ruben ,

let say my expression is :

=$(=FirstSortedValue('[' & $(vDim) & ']' , _DimPosition , 12)) , then does it means that i want :

value of vDim

which is at 12th position

according to _DimPosition.

Is it so??

Re: First Sorted Value Expression Prob

awhitfield — Tue, 17 Mar 2015 09:04:13 GMT

Yes it does.

Re: First Sorted Value Expression Prob

nikhilgarg — Tue, 17 Mar 2015 09:11:48 GMT

Thankx Andrew

Re: First Sorted Value Expression Prob

nikhilgarg — Tue, 17 Mar 2015 09:20:27 GMT

Hey,

One question more:

If i have 3 enable condition as:

GetSelectedCount(_Dim) >=1

GetSelectedCount(_Dim) >=2

GetSelectedCount(_Dim) >=3

And now if i select 3 dimensions then , wil all 3 enable conditions execute or only 3rd enable condition execute ?

Re: First Sorted Value Expression Prob

rubenmarin — Tue, 17 Mar 2015 09:24:08 GMT

If each enable condition is assigned to each expression, all 3 are enabled, as each enable condition is true.

Re: First Sorted Value Expression Prob

nikhilgarg — Tue, 17 Mar 2015 09:30:56 GMT

Yeah actually all three enable condition is assigned to 3 different Calculated Dimension. Thanks i wanted to clear this doubt. And if i am not wrong if i select 3 dimension then all three enable condition and calculated dimension are executed to get 1st , 2nd and 3rd dimension as per _DimPosition field . For eg:

If i select dimension at pos 12 , 34 and 8 then final straight table contain columns starting with dim 8, 12 and 34 position. Is it ??

Re: First Sorted Value Expression Prob

rubenmarin — Tue, 17 Mar 2015 09:59:48 GMT

I'll need to check a sample to be sure, but that's what I understand it does.

Re: First Sorted Value Expression Prob

nikhilgarg — Tue, 17 Mar 2015 10:19:24 GMT

Ohk but in case if you create sample and observer then let me know. Thankx