topic Re: Script to get final piece of data in QlikView

Script to get final piece of data

raynac — Thu, 29 May 2014 15:44:42 GMT

I have a view that accesses phone call records. The call can be answered, transferred, sent to voicemail etc, and each action adds a record to the table. The session id marks the call and each sequence number marks each action, so a call that gets transferred around will have the same sessionID but can (and usually does) have several sequence numbers.

I need to be able to just look at the call status of that *last* sequence number to see how the call ended.

I have tried getting the last record and then using that to join back to a second instance of the table to get the last activity state, but it does not work. I still get all actions, not just the last one.

lastActivity:

Load

SessionID,

max(SequenceID) as "Last Activity"

group By SessionID;

SQL SELECT *

FROM database

lastActivityState:

Load

SessionID,

SequenceID as "Last Activity",

ExitState as "Last Activity State"

;

SQL SELECT *

FROM database

I tried max(exitState) but, of course, that gives me the highest exist state numerically which is not what I need. I have also tried to concatenate the session and max sequence ID to get a single key, but that does not work either. But I am willing to live with the synthetic key, if I can just get it to work.

Can anyone please offer any direction?

Thank you!

Re: Script to get final piece of data

Greg_Williams — Thu, 29 May 2014 15:46:42 GMT

What about Peek(<field>,-1)

-1 is the last record

Will this work?

Re: Script to get final piece of data

Thu, 29 May 2014 15:59:33 GMT

If I understand correctly, since you already have the correct SequenceID "Last Activity", you can map this to your data and tag that row.

lastActivity:

Load

SessionID,

max(SequenceID) as "Last Activity"

group By SessionID;

SQL SELECT *

FROM database

lastActivity_map:

Mapping load

[Last Activity], 1 as LastTag

resident lastActivity;

lastActivityState:

Load

SessionID,

applymap('lastActivity_map',SequenceID,0) as "isLastActivity"

ExitState as "Last Activity State"

To get the last ExitState just use set analysis - {< isLastActivity = {'1'} >}

Re: Script to get final piece of data

raynac — Thu, 29 May 2014 19:46:08 GMT

Hi Greg - not that I can see! It keeps giving me errors, if I try to use an "Order By" statement and all the records, if I don't.

Maybe I am doing something wrong?

Re: Script to get final piece of data

maxgro — Thu, 29 May 2014 19:54:02 GMT

only record with last

lastActivity:
Load
SessionID,
max(SequenceID) as "Last Activity"
group By SessionID;
SQL SELECT *
FROM database;

left join (lastActivity)
Load
SessionID,
SequenceID as "Last Activity",
ExitState as "Last Activity State";
SQL SELECT *
FROM database;

another, all records, last by session flagged

tmp:
Load
SessionID,
SequenceID as "Last Activity",
ExitState as "Last Activity State";
SQL SELECT *
FROM database;


final:
noconcatenate 
load *, if(peek(SessionID)<>SessionID  ,1, 0) as flag
resident tmp
order by SessionID, SequenceID desc; 


drop table tmp;


drop table tmp;

Re: Script to get final piece of data

Anonymous — Thu, 29 May 2014 19:57:03 GMT

Hello, Rayna.

Your solution with join solve your problem. Did you use a prefix with the join or a plain join? In QlikView, unlike SQL, a plain join is an outer join. Your idea requires an inner join. Just put the prefix in front of your join and it should work.

Hope it helps.

Bruno.

Re: Script to get final piece of data

raynac — Thu, 29 May 2014 20:19:07 GMT

Patrick,

Thank you so much for your detailed reply. I remain convinced that it should work and yet after playing with this for the better part of two hours, I cannot seem to make it do what I want.

When I do a quick peruse of the resultant table, all the "isLastActivity" fields are 1s...even those that should not be. As a result, the only records for which I get the right Last Activity State are the ones where Sequence 1 is actually the last activity record.

I don't do much mapping - what does this line do? "[Last Activity], 1 as LastTag"

Re: Script to get final piece of data

preminqlik — Thu, 29 May 2014 20:25:19 GMT

lastActivity:

Load

SessionID,

lastvalue(SequenceID) as [Last Activity]

group By SessionID;

SQL SELECT *

FROM database

lastActivityState:

Load

SessionID,

SequenceID as "Last Activity",

ExitState as "Last Activity State"

;

SQL SELECT *

FROM database

Re: Script to get final piece of data

raynac — Thu, 29 May 2014 20:30:36 GMT

HI Massimo,

You are awesome - thank you! That first solution worked like a charm!!

Thanks again 😄

Rayna

Re: Script to get final piece of data

raynac — Thu, 29 May 2014 20:31:58 GMT

Hi Bruno,

The only reason I could not get this to work was because I was saving the max(sequenceID) with a new name (Last Activity, I think) and then I could not figure out how to use the new name to do an inner join with another table!

Rayna

Re: Script to get final piece of data

raynac — Thu, 29 May 2014 20:34:29 GMT

Hi Prem Kumar,

That is nearly exactly what I was trying but I was using max() instead of lastvalue(). I still was getting all values in the second table, because Qlikview defaults not to an equal join but an outer one.

I have a solution now but for future reference, does the lastvalue() function make a difference?

Rayna

Re: Script to get final piece of data

preminqlik — Thu, 29 May 2014 20:42:52 GMT

yes lastvalue() always gives latest value , i.e last record .now for getting second table solved use left keep after using lastvalue() like below

lastActivity:

Load

SessionID,

lastvalue(SequenceID) as [Last Activity]

group By SessionID;

SQL SELECT *

FROM database

left keep

lastActivityState:

Load

SessionID,

SequenceID as [Last Activity],

ExitState as [Last Activity State]

;

SQL SELECT *

FROM database

Re: Script to get final piece of data

raynac — Thu, 29 May 2014 21:10:16 GMT

That totally worked too!

Thank you so much! Everyone here is so helpful. I don't know what I'd do without this community.

Re: Script to get final piece of data

Mon, 02 Jun 2014 19:45:02 GMT

"[Last Activity], 1 as LastTag" - this maps '1' to all last SequenceIDs, to achieve something similar with Massimo's second solution's field flag.