topic Re: accumulation in script in QlikView

accumulation in script

Fri, 10 Aug 2012 21:11:44 GMT

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!

Re: accumulation in script

swuehl — Fri, 10 Aug 2012 21:38:48 GMT

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

Re: accumulation in script

Mon, 13 Aug 2012 13:05:13 GMT

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

Thanks for your help!

Re: accumulation in script

swuehl — Mon, 13 Aug 2012 13:09:57 GMT

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

Re: accumulation in script

Mon, 13 Aug 2012 13:13:16 GMT

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

Re: accumulation in script

swuehl — Mon, 13 Aug 2012 13:16:28 GMT

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?

Re: accumulation in script

Mon, 13 Aug 2012 13:54:45 GMT

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

Re: accumulation in script

swuehl — Mon, 13 Aug 2012 14:14:44 GMT

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

Re: accumulation in script

Mon, 13 Aug 2012 15:04:41 GMT

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!

Re: accumulation in script

Fri, 17 Aug 2012 17:51:38 GMT

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!

Re: accumulation in script

swuehl — Fri, 17 Aug 2012 21:26:25 GMT

Try using peek() instead of previous().

Then I think you may need to sort also by EventDateTime?

If you are only interested in the sum(LoginLogoff), you can also use a group by load like

DingoLog2:

LOAD

DingoUserName, StreamId, DistalHostIp, sum(LoginLogoff) as LoginsOnThisHost2

Resident DingoLog group by DingoUserName, StreamId, DistalHostIp;

DROP Table DingoLog;