How to return a specific value based on the latest date of record

[vsevolozhskiy]

HI!

I have a table with many lines and columns,

One of the columns is “date of entry”  another Column “Production Department” and another one is “Product Name”

How can I return latest entry (latest “date of entry” ) for „Product Name“ based on the “Production Deprtment”

date of entry	Production Department	Product Name
18.04.2018	MF1	A
18.04.2018	MF2	A
18.04.2018	MF3	B
19.04.2018	MF1	C
19.04.2018	MF2	D
19.04.2018	MF3	B
19.04.2018	MF4	E

1. e.g.

IF I chose MF1 I need to see product C

Many thanks in advance!

Sergey

[tomasz_tru]

for example:

input:

LOAD * INLINE [

"date of entry" "Production Department" "Product Name"

18.04.2018 MF1 A

18.04.2018 MF2 A

18.04.2018 MF3 B

19.04.2018 MF1 C

19.04.2018 MF2 D

19.04.2018 MF3 B

19.04.2018 MF4 E

](delimiter is '\t');

//filter

RIGHT JOIN (input)

LOAD "Production Department", MAX("date of entry") AS "date of entry" RESIDENT input

GROUP BY "Production Department";

[vsevolozhskiy]

Hi Tomasz,

thanks a lot for a fast response!

if I understood you correctly you suggest to use load manger, yes?

It is possible to do as KPI Chart object?

So I choose MF1 in a Filter object and KPI Chart object shows “Product name C”

Many thanks in advance!

Sergey

[tomasz_tru]

It's better to avoid complicated calculations on the front-end. The output of this script will be able to show the data you need. Just make sure there's no different multiple entries for the same data and the same department.

Then use "only([Product Name])" as a measure for KPI object.

Tomasz

[tomasz_tru]

But if you insist on formula, try sth like this:

FirstSortedValue([Product Name],-[date of entry])