Retrieve one object at a specific time using Set Analyze

[#1]

Hello

I am trying to retrieve the last value for and object.

sum( {$<SYSTIME = {$(#=MAX(SYSTIME))},OBENAME ={'XXXXXXXXXXXXXXXXXXX'}>} VALUE )

This does not work but

SUM ( {$<Rowid={$(#=MAX(Rowid))},OBENAME={'AMHEG2BUSGEN_P'}> } VALUE) almost works.

The only problem is that when I select OBENAME in other dialog then my aggregation doesn't return any data.

I would like to use SYSTIME instead of rowid and be able to retrieve an object even though a selection of an object has been done in other dialog.

Table Viewer

Script

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 kr;-#.##0,00 kr';

SET TimeFormat='hh:mm:ss';

SET DateFormat='YYYY-MM-DD';

SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';

SET MonthNames='jan;feb;mar;apr;maj;jun;jul;aug;sep;okt;nov;dec';

SET DayNames='må;ti;on;to;fr;lö;sö';

ODBC CONNECT TO [xxxx;DBQ=vvvv ] (XUserId is ECaDLUFNTLIA, XPassword is IQbQCUFNTTYB);

LOAD IRN as ID, NAME;

SQL SELECT IRN, NAME FROM OBE;

LOAD "OBE_IRN" as ID, SYSTIME, VALUE,(Day(SYSTIME)*24*60*60+hour(SYSTIME)*60+minute(SYSTIME)*60+second(SYSTIME)) as Rowid;

SQL SELECT * FROM HIS.LMEAONESECOND where systime > sysdate-1;

/Kjell Bystedt

[#2]

You can put the timestamp function right into your dollar sign expansion.

SUM( {1<Systime={$(#=timestamp(MAX(Systime)))},NAME={'SAIP400BAY5ACPOWER'}> } VALUE)

You'll probably need to look at the evaluated dollar sign expansion again to make sure it's coming out in the format you want in the end.

EDIT: I think you need to get rid of the pound symbol in your equation though. It may even work without the timestamp or the pound sign. The pound sign forces the answer to be a number and you don't want it to be a number.

SUM( {1<Systime={"$(=timestamp(MAX(Systime)))"},NAME={'SAIP400BAY5ACPOWER'}> } VALUE)

OR

SUM( {1<Systime={"$(=MAX(Systime))"},NAME={'SAIP400BAY5ACPOWER'}> } VALUE)

[#3]

Hello

Thank you

It works better now but my final problem with this expression is that

I get max(SYSTIME) for the object that has max(SYSTIME) not the individual object

MAX(SYSTIME)
2009-11-24 13:37:29
2009-11-24 13:37:29

SUM ( {1<SYSTIME={$(=MAX(SYSTIME))},NAME={'AM01400BAY6ACPOWER'}> } VALUE) SUM( {1<SYSTIME={2009-11-24 13:37:29},NAME={'AM01400BAY6ACPOWER'}> } VALUE)
The max for AM01400BAY6ACPOWER is 2009-11-24 13:00:00 How do I get the max for 'AM01400BAY6ACPOWER'