Selecting last value with expression

[#1]

Hi,

I have an SQL table with salary information:

per_id	salary	start_period
1	100	201101
1	120	201204
1	140	201402

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]

Try something like

INPUT:

LOAD per_id,

     salary,

     start_period

FROM

[http://community.qlik.com/thread/119681]

(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 · Accepted solution]

Use per_id as your Dimension. For your expression:

=FirstSortedValue(salary,-start_period)

-Rob

http://masterssummit.com

http://robwunderlich.com

[#4]

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