Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
What about Peek(<field>,-1)
-1 is the last record
Will this work?
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'} >}
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?
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;
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.
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"
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
HI Massimo,
You are awesome - thank you! That first solution worked like a charm!!
Thanks again 😄
Rayna
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