Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Employee
Employee

Counting unique 24 hour occurrences

I'm trying to create a customer service application. We have data that has timestamps for calls.

We have the concept of ‘conversations’. That is any time a customer calls multiple times over a 24 hour period that counts as a single unique conversation.

Let’s say customer 3 has one conversation and customer 4 has 2 conversations. That’s ‘cause customer 3 called all within 24hrs and customer 4 called once beyond 24 hrs. So the data looks like this:

DateCustID
5/2/2014 2:003
5/2/2014 3:003
5/2/2014 17:003
5/3/2014 1:003
5/2/2014 3:004
5/2/2014 4:004
5/2/2014 18:004
5/3/2014 2:004
5/3/2014 4:004

We want the load script to track the unique conversations. I guess the thing to do would be to have each record track the 1st time within 24 hours the customer called us. So when QV's script is done it looks like this:

DateCustIDStartConversationConversationLengthUniqueVisits
5/2/2014 2:0035/2/2014 2:0001
5/2/2014 3:0035/2/2014 2:0010
5/2/2014 17:0035/2/2014 2:00150
5/3/2014 1:0035/2/2014 2:00230
5/2/2014 3:0045/2/2014 3:0001
5/2/2014 4:0045/2/2014 2:0010
5/2/2014 18:0045/2/2014 2:00150
5/3/2014 2:0045/2/2014 2:00230
5/3/2014 4:0045/3/2014 4:0001

Obviously when the conversation length reaches 24 hours, it acts like a new conversation so the conversation length would never reach 25.

Any ideas how to do something like this? I've attached a sample excel file with 2 tabs from above.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Counting unique 24 hour occurrences

Maybe like attached

2 Replies
MVP
MVP

Re: Counting unique 24 hour occurrences

RESULT

1.png

SCRIPT

see attachment

MVP
MVP

Re: Counting unique 24 hour occurrences

Maybe like attached

Community Browser