topic Retrieve one object at a specific time using Set Analyze in QlikView

Retrieve one object at a specific time using Set Analyze

Tue, 24 Nov 2009 20:25:47 GMT

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

Retrieve one object at a specific time using Set Analyze

Tue, 24 Nov 2009 21:39:10 GMT

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.

Retrieve one object at a specific time using Set Analyze

Tue, 24 Nov 2009 22:00:54 GMT

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.

Retrieve one object at a specific time using Set Analyze

Tue, 24 Nov 2009 22:24:47 GMT

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

Retrieve one object at a specific time using Set Analyze

Wed, 25 Nov 2009 00:31:43 GMT

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

Retrieve one object at a specific time using Set Analyze

Wed, 25 Nov 2009 01:20:51 GMT

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'.

Retrieve one object at a specific time using Set Analyze

Wed, 25 Nov 2009 19:49:43 GMT

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?

Retrieve one object at a specific time using Set Analyze

Wed, 25 Nov 2009 22:52:03 GMT

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.

Retrieve one object at a specific time using Set Analyze

Tue, 01 Dec 2009 04:31:10 GMT

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)

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)

Retrieve one object at a specific time using Set Analyze

Tue, 01 Dec 2009 04:31:11 GMT

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)

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)

Retrieve one object at a specific time using Set Analyze

Tue, 01 Dec 2009 05:42:48 GMT

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)

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

Retrieve one object at a specific time using Set Analyze

Tue, 01 Dec 2009 16:20:05 GMT

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'

Retrieve one object at a specific time using Set Analyze

Tue, 01 Dec 2009 16:22:13 GMT