topic Re: FirstSortedValue cannot be used in script in QlikView

FirstSortedValue cannot be used in script

ananyaghosh — Fri, 20 Feb 2015 05:12:03 GMT

Hi,

I have done this thing:

Employee:

load * Inline [

Employee ID, Age, Customer Name, Sales

Sandip Ghosh, 34, Customer1, 1000

Ananya Ghosh, 29, Customer2, 1200

Amit Das, 35, Customer3, 3000

Kaushik Dhar, 55, Customer4, 5400

Sudhir Ghosh, 74, Customer5, 5980

Sandip Ghosh, 34, Customer6, 54670

Sandip Ghosh, 34, Customer7, 45675

Kaushik Dhar, 55, Customer8, 15400

Kaushik Dhar, 55, Customer9, 45400

Sudhir Ghosh, 74, Customer10, 6598

Ananya Ghosh, 29, Customer2, 16200

Sudhir Ghosh, 74, Customer5, 5980

Sudhir Ghosh, 74, Customer6, 2980

Ananya Ghosh, 29, Customer2, 12900

Ananya Ghosh, 29, Customer2, 12070

];

LET vHighestSalary = FirstSortedValue([Customer Name], -Sales);

SET vSalary = 'Highest Salary = ' & vHighestSalary;

Now show the variable in textbox object as =$(vSalary), but it only show 'Highest Salary = ' and value is not showing.

Re: FirstSortedValue cannot be used in script

jonathandienst — Fri, 20 Feb 2015 05:15:12 GMT

It needs to be in a load statement:

T_Fsv:

LOAD FirstSortedValue([Customer Name], -Sales) As Fsv

Resident Employee;

Let vHighestSalary = Peek('Fsv');

Let vSalary = 'Highest Salary = ' & vHighestSalary;

DROP TABLE T_Fsv;

HTH

Jonathan

Re: FirstSortedValue cannot be used in script

anbu1984 — Fri, 20 Feb 2015 05:27:18 GMT

You can directly write the below expression in Text box or you assign the FirstSortedValue() expression to a variable in UI using Variable overview

='Highest Salary = ' & FirstSortedValue([Customer Name], -Sales)

Re: FirstSortedValue cannot be used in script

ananyaghosh — Fri, 20 Feb 2015 05:41:26 GMT

If semicolon is used at the end of the script it will give garbage error. So drop that semicolon : ;

Re: FirstSortedValue cannot be used in script

anbu1984 — Fri, 20 Feb 2015 05:47:48 GMT

You are right. Fixed the previous post

Re: FirstSortedValue cannot be used in script

ananyaghosh — Tue, 24 Mar 2015 10:36:43 GMT

Can it be written as:

T_Fsv:

LOAD [Customer Name], max(Sales) As Fsv

Resident Employee group by [Customer Name];

Let vHighestSalary = Peek('Fsv');

Let vSalary = 'Highest Salary = ' & vHighestSalary;

DROP TABLE T_Fsv;

Re: FirstSortedValue cannot be used in script

Peter_Cammaert — Tue, 24 Mar 2015 11:02:49 GMT

Or change your script by dropping the LET vHighestSalary... statement and modifying the SET statement into something like:

SET vSalary='Highest Salary = ' & FirstSortedValue([Customer Name], -Sales);

No need to change any expressions in the UI.

Re: FirstSortedValue cannot be used in script

ananyaghosh — Tue, 24 Mar 2015 12:43:32 GMT

I have tried this:-

LET vHighestSalary = FirstSortedValue([Customer Name], -Sales);

SET vSalary = 'Highest Salary = ' & vHighestSalary;

but it does not work for me.

Re: FirstSortedValue cannot be used in script

anbu1984 — Tue, 24 Mar 2015 13:16:38 GMT

LET vHighestSalary = 'FirstSortedValue([Customer Name], -Sales)';

SET vSalary = 'Highest Salary = ' & $(vHighestSalary);

In Text box, use this expr

=$(=vSalary )

Re: FirstSortedValue cannot be used in script

Peter_Cammaert — Tue, 24 Mar 2015 14:22:42 GMT

That's because the LET statement evaluates the expression on the right of the equal sign, and that part is invalid (or doesn't return a value).

The SET statement doesn't do a such thing. It just stores everything after the equal sign as literal text in your variable for later use.

Drop the LET statement, and use the SET statement version from my post.

Or use the version that Anbu posted.

Re: FirstSortedValue cannot be used in script

ananyaghosh — Wed, 25 Mar 2015 03:58:00 GMT

Can u tell me what is the use of double equals to(=) and $ sign here? or why I am using = sign insight the () ?

Please give an explanation with an example.

Re: FirstSortedValue cannot be used in script

anbu1984 — Wed, 25 Mar 2015 04:10:42 GMT

Check these links

The Magic of Variables

The Magic of Dollar Expansions

Re: FirstSortedValue cannot be used in script

Peter_Cammaert — Wed, 25 Mar 2015 07:22:04 GMT

There is no double equal sign in my examples. Moreover, you won't need it.

This is the theory:

The first equal sign is necessary for QlikVies to interprete the text as an expression (just like in Excel). First the text after the first equal sign will be evaluated as a regular QlikView expression and the result of evaluation will be used/displayed.
$-sign substitution always comes before any evaluation.
Usually a $(varname) construct will we replaced by the text value that is stored in variable varname. Varname can be prefixed by one of two 'modifiers':
- a # means substitute with the numerical value of varname
- a = means substitute with the result of evaluating the text after the equal sign (again an expression)

Best,

Peter