accumulation in script

[#1]

I am trying to get a new field that is an accumulation of another field.

i can do this on front end - using RangeSum(Above(LoginLogoff,0,RowNo()))   .  This works just fine.

i am trying to do this during the intial load using -  if ((Previous(iKeyPart1)=iKeyPart1),Peek('ActiveLogins'),0)+LoginLogoff as ActiveLogins

It is giving me strange results. it seems to be working until the accumulation reaches 3. Then i start seeing values that dont quite make sense.

Need help!

[swuehl]

I am quite unclear how your fields in the load script relate to the columns / dimensions in your chart.

For example, I can't see your iKeyPart1 field in the chart table. And in your chart, the column named Login / Logoff seems to have text values, while you numerically add this (same?) field in your script.

Could you post a small sample file? Upload is available in advanced editor.

Regards,

Stefan

[#3]

Sorry for the confusion. Hopefully this will make more sense.

Thanks for your help!

[swuehl]

Can you also post the relevant part of script? And have you sorted your input table appropriately?

[#5]

Here is that part of the script -

Load1:
LOAD Month(EventDateTime) as LogMonth,
Day(EventDateTime) as LogDay,
EventDateTime,
    StreamId as stream,
    StreamName,
    DingoUserName as iKeyPart1,
    EventResult,
    EventResultText,
    EventTrigger,
    If (EventTrigger='ClientRequest',1,-1) as LoginLogoff;
SQL SELECT *
FROM raw_entitlement_journal
WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= EventDateTime and EventResultText = 'Success';

Load2:
LOAD Month(EventDateTime) as LogMonth,
Day(EventDateTime) as LogDay,
EventDateTime,
stream,
    StreamName,
    iKeyPart1,
    EventResult,
    EventResultText,
    EventTrigger,
    LoginLogoff,
    if ((Previous(iKeyPart1)=iKeyPart1),Peek('ActiveLogins'),0)+LoginLogoff as ActiveLogins
    Resident Load1 Order by iKeyPart1, stream, EventDateTime;

Thanks,

Pulasti

[swuehl]

This will create a lot of common fields between Load1 and Load2 table, resulting in a large synthetic key / table, right?

Have you dropped your first table Load1?

[#7]

Hi Stefan,

  I had not dropped the table. But even after dropped the table, the numbers did not come out right.

I am attaching the qv file.

Thanks a lot for looking into it!

Regards,

Pulasti

[swuehl · Accepted solution]

I think a main issue here is that you've got multiple events (up to some 10)  per unique time stamp. In your example, you've got 2 Login events 8/6/2012 7:50:05 AM.

In your table, you only count and accumulate one event (try using sum(LoginLogoff) in your chart!), while the accumulated script field is ambiguous (two possible values for that Timestamp!) and thus a '-' is returned by QV (try for example using =min(ActiveLogins) in your chart expression!).

So if you handle the multiple events accordingly, you should be able to fix your accumulation issue, too.

Regards,

Stefan

[#9]

Great!

Thanks a lot Stefan. I did not realize that there could be multiple login/logoff events on subsecond level.

My script is now working as expected!

[#10]

still struggling with similar issue.

Could you please help me with the attached small example? The code is on tab main2.

I am not getting LoginsOnThisHost counts ...most of them are NULL(?)

Appreciate your help!