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 should check the format of your SYSTIME and see what you are getting out of the dollar sign expansion. Set up that Set Analysis expression in a table, but don't give it a label. Then when the table is rendered, you should see the Set Analysis expression with the value of the dollar sign expansion evaluated. I have had QlikView do some formatting on dates in this situation.

As for your second issue, I'm not quite sure. Are you selecting 'AMHEG2BUSGEN_P' from OBENAME or something else. If you select something else, you should get nothing, because your Set Analysis is looking for a value that is not in the selected set. If you want to be able to select anything and still have this expression display for 'AMHEG2BUSGEN_P' then you need to change {$... to {1... so it will ignore selections.

[#3]

Hello

I am looking to the first problem.

For the second problem I am selecting AMHEG2BUSGEN_P' from OBENAME.

I changed the query to:

SUM

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

It did to help. Some how it has to be a associated with OBENAME.

Where should I put the 1.

[#4]

I was talking about the one at the very beginning. You have it on your new expression. Is that working for you now?

[#5]

No I get "No data to display". Maybe it is the max expression that is the problem. I what the max time of the

OBENAME. Maybe I have to use the aggr function. Do you have any idea?

/Kjell

[#6]

Use my suggestion from the first message. It is really the best thing you can do when troubleshooting Set Analysis.

You should check the format of your SYSTIME and see what you are getting out of the dollar sign expansion.
Set up that Set Analysis expression in a table, but don't give it a label. Then when the table is rendered,
you should see the Set Analysis expression with the value of the dollar sign expansion evaluated.

Once you have the expression that QlikView is using (that isn't working), you will have a better idea of what the problem is.

I think the problem you are having is you are searching for the greatest ROWID, but not the greatest ROWID with 'AMHEG2BUSGEN_P'. Try this:

SUM( {1<Rowid={$(#=MAX(Rowid{1<OBENAME={'AMHEG2BUSGEN_P'}>}))}>} VALUE)

That should give you the greatest ROWID for AMHEG2BUSGEN_P, then you'll sum the values where the ROWIDs match. If ROWID is unique (I assume), then you will get the value of the greatest ROWID for 'AMHEG2BUSGEN_P'.

[#7]

Hello

Sorry I am new in qlikview. What do you mean with a table?

• DB table?
• QV table?
• Table box?

Do you have a simple example?

[#8]

Sorry, I meant a Straight Table Chart or a Pivot Table Chart. I've attached a little sample file. In the file, the second expressions is:

Sum({<FK = {$(=Max(FK))}>} Sales)

On the table, I have not given this expression a label, so the label is the expression with the dollar sign expansion evaluated. In this case:

Sum({<FK = {7}>} Sales)

Here, it doesn't seem that useful, but if you have a date or a more complex expression, it can be very helpful.

Another thing to try is to remove the Max from your expression. Instead, hard code the Max ROWID from your data set. Something like:

SUM({1<Rowid={10124},OBENAME={'AMHEG2BUSGEN_P'}> } VALUE)

Once you have the Set Analysis working with hardcoded values, then go back and work on getting that hard coded value from an expression.

[#9]

Thank You for the example

It seems to be a problem with the time format.

SUM

gives the follwing result:

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

SUM( {$<SYSTIME={40141.567696759}> } VALUE)

0

timestamp(40141.567696759)

Gives

2009-11-24 13:37:29

How can I use the timestamp function in the expression

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

[#10]

Thank You for the example

It seems to be a problem with the time format.

SUM

gives the follwing result:

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

SUM( {$<SYSTIME={40141.567696759}> } VALUE)

0

timestamp(40141.567696759)

Gives

2009-11-24 13:37:29

How can I use the timestamp function in the expression

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