topic Re: Selecting last value with expression in QlikView

Selecting last value with expression

Sun, 25 May 2014 04:04:48 GMT

Hi,

I have an SQL table with salary information:

per_id: Person ID

salary: the salary

start_period: year and month (yyyyMM) when the salary changed.

I'd like to put in a table the salary for a given period, for exemple 201308.

I can do this in SQL but I have no cloue on how to put this value in a table when I have selected the period.

This is the SQL query I would use:

SELECT top 1 per_id

,salary

,start_period

FROM salary

WHERE per_id=1

and start_period<=201308

ORDER BY start_period DESC

thank you!

Diego

swuehl — Sun, 25 May 2014 12:54:37 GMT

Try something like

INPUT:

LOAD per_id,

salary,

start_period

FROM

(html, codepage is 1252, embedded labels, table is @1);

LOAD per_id,

FirstValue(salary) as CurrentSalary

Resident INPUT

where start_period <= 201308

Group by per_id

order by start_period desc;

rwunderlich — Sun, 25 May 2014 13:42:58 GMT

Use per_id as your Dimension. For your expression:

=FirstSortedValue(salary,-start_period)

-Rob

Sun, 25 May 2014 14:46:04 GMT

Just what I was looking for, works great! Thank you very much!