Script to get final piece of data

[raynac]

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!

[Greg_Williams]

What about Peek(<field>,-1)

-1 is the last record

Will this work?

[#3]

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

[raynac]

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?

[maxgro · Accepted solution]

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;

[Anonymous]

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.

[raynac]

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"

[preminqlik]

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

[raynac]

HI Massimo,

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

Thanks again 😄

Rayna

[raynac]

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