Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
leomathew
New Contributor III

Load only latest records?

Hi,

i have the following table

EmpDeptcreditEffective_dt
A10A101/1/2015
A12A2001/12/2014
A9B3001/11/2014
B10A902/10/1999
B9B801/9/2014
C9B251/1/2014

Out of which i want to load only the latest record for each of the employee in qlikview.

i.e

the table has all historical data, out of which i want to fect latest record for each employee.

the desired table should have the following records

EmpDeptcreditEffective_dt
A10A101/1/2015
B9B801/9/2014
C9B25

1/1/2014

Kindly suggest the script.

Regards

Vidit

Tags (3)
1 Solution

Accepted Solutions
Employee
Employee

Re: Load only latest records?

LOAD

  Emp,

  firstsortedvalue(Dept,-Effective_dt) as Dept,

  firstsortedvalue(credit,-Effective_dt) as credit,

  date(max(Effective_dt)) as Effective_dt

Group by Emp;

LOAD * INLINE [

    Emp, Dept, credit, Effective_dt

    A, 10A, 10, 1/1/2015

    A, 12A, 200, 1/12/2014

    A, 9B, 300, 1/11/2014

    B, 10A, 90, 2/10/1999

    B, 9B, 80, 1/9/2014

    C, 9B, 25, 1/1/2014

];

Please note we use "-" as sort order because we want the newest values

6 Replies
giakoum
Honored Contributor II

Re: Load only latest records?

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,

firstsortedvalue(PurchasedArticle, OrderDate) as FirstProductBought from abc.csv

group by Customer;

Employee
Employee

Re: Load only latest records?

LOAD

  Emp,

  firstsortedvalue(Dept,-Effective_dt) as Dept,

  firstsortedvalue(credit,-Effective_dt) as credit,

  date(max(Effective_dt)) as Effective_dt

Group by Emp;

LOAD * INLINE [

    Emp, Dept, credit, Effective_dt

    A, 10A, 10, 1/1/2015

    A, 12A, 200, 1/12/2014

    A, 9B, 300, 1/11/2014

    B, 10A, 90, 2/10/1999

    B, 9B, 80, 1/9/2014

    C, 9B, 25, 1/1/2014

];

Please note we use "-" as sort order because we want the newest values

leomathew
New Contributor III

Re: Load only latest records?

Hi,

Thanks for your reply.

Do i also need to sort my data on Effective_dt, before using the firstsortedvalue function??

Regards

Vidit

Employee
Employee

Re: Load only latest records?

No, no need of sorting your data, firstsortedvalue will handle it

leomathew
New Contributor III

Re: Load only latest records?

ok, nice.

Thanks buddy

Employee
Employee

Re: Load only latest records?

you are welcome

Community Browser