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!

[raynac]

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

[preminqlik]

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

[raynac]

That totally worked too!

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

[#5]

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