Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
vsevolozhskiy
New Contributor

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

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

  1. 18.04.2018

MF1

A

  1. 18.04.2018

MF2

A

  1. 18.04.2018

MF3

B

  1. 19.04.2018

MF1

C

  1. 19.04.2018

MF2

D

  1. 19.04.2018

MF3

B

  1. 19.04.2018

MF4

E

  1. e.g.

IF I chose MF1 I need to see product C

Many thanks in advance!

Sergey

4 Replies
tomasz_tru
Valued Contributor

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

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
New Contributor

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

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
Valued Contributor

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

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
Valued Contributor

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

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

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