Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
raynac
New Contributor III

Script to get final piece of data

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!

Tags (4)
1 Solution

Accepted Solutions
MVP
MVP

Re: Script to get final piece of data

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;



13 Replies
Employee
Employee

Re: Script to get final piece of data

What about Peek(<field>,-1)

-1 is the last record

Will this work?

Not applicable

Re: Script to get final piece of data

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
New Contributor III

Re: Script to get final piece of data

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?

MVP
MVP

Re: Script to get final piece of data

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;



bruno_montenegr
Contributor III

Re: Script to get final piece of data

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
New Contributor III

Re: Script to get final piece of data

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
Valued Contributor II

Re: Script to get final piece of data

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
New Contributor III

Re: Script to get final piece of data

HI Massimo,

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

Thanks again :-D

Rayna

raynac
New Contributor III

Re: Script to get final piece of data

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

Community Browser