topic Re: Maxstring in Search the Community

Maxstring

AnnaQlik — Tue, 25 Jan 2022 10:29:34 GMT

I have a timestamp for every activity that is made in an object. I would like to have a table that only shows the latest year of activity per objectID.

Now it looks like this:

ObjectID datDatum

38 2011-02-15 12:00:00 AM

38 2010-04-18 12:00:00 AM

38 2009-11-20 12:00:00 AM

46 2017-08-26 14:00:00 AM

And I want it to look like this:

ObjectID datDatum

38 2011

46 2017

If I use =year(datDatum) in the chart calculated dimension the result is the following:

ObjectID datDatum

38 2011

38 2010

38 2009

46 2017

I have tried to elaborate with maxstring but cant make it work, i only get error in the calculated dimension as a result. Can anyone please help me?

Re: Maxstring

rwunderlich — Tue, 25 Jan 2022 19:44:08 GMT

For a Date it would be Max(), not MaxString().

year(Max(datDatum))

-Rob

Re: Maxstring

Qrishna — Wed, 26 Jan 2022 01:36:45 GMT

Hi @AnnaQlik - Rob's Solution works fine.

Attaching the file below.

if you're not able to open the attached, then copy the script below.

Another Scripting approach(My fav Approach - to do as much as possible at the script level to minimize UI expressions) :

data:
load *,
num(left(datDatum, 4)) as year;
load * inline [
ObjectID, datDatum
38, 2011-02-15 12:00:00 AM
38, 2010-04-18 12:00:00 AM
38, 2009-11-20 12:00:00 AM
46, 2017-08-26 14:00:00 AM
];

max:
load ObjectID,
max(year) as max_year
resident data
group by ObjectID;