topic Re: Load only latest records? in QlikView

Load only latest records?

Vidit — Tue, 03 Feb 2015 14:54:58 GMT

Hi,

i have the following table

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

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

Emp	Dept	credit	Effective_dt
A	10A	10	1/1/2015
B	9B	80	1/9/2014
C	9B	25	1/1/2014

Kindly suggest the script.

Regards

Vidit

Re: Load only latest records?

giakoum — Tue, 03 Feb 2015 14:58:20 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,

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

group by Customer;

Re: Load only latest records?

Clever_Anjos — Tue, 03 Feb 2015 15:17:22 GMT

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

Re: Load only latest records?

Vidit — Tue, 03 Feb 2015 15:24:47 GMT

Hi,

Thanks for your reply.

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

Regards

Vidit

Re: Load only latest records?

Clever_Anjos — Tue, 03 Feb 2015 15:31:11 GMT

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

Re: Load only latest records?

Vidit — Tue, 03 Feb 2015 15:34:36 GMT

ok, nice.

Thanks buddy

Re: Load only latest records?

Clever_Anjos — Tue, 03 Feb 2015 15:36:25 GMT

you are welcome