topic Re: How to return a specific value based on the latest date of record in App Development

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

vsevolozhskiy — Thu, 19 Apr 2018 12:14:18 GMT

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

e.g.

IF I chose MF1 I need to see product C

Many thanks in advance!

Sergey

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

tomasz_tru — Thu, 19 Apr 2018 14:49:01 GMT

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";

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

vsevolozhskiy — Fri, 20 Apr 2018 10:43:27 GMT

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

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

tomasz_tru — Tue, 24 Apr 2018 10:03:25 GMT

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

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

tomasz_tru — Tue, 24 Apr 2018 10:21:34 GMT

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

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