How to extract phrases from text in a load

[Anonymous]

Hi there,

I am trying to extract some data from these fields in order to do some reporting on it

Below is a sample of the text in the field "ENTRY_TEXT"

For example I would like to run reports on the time a ticket is created until it goes to resolved.  Unfortunately the field for status only shows the last stats which int his case is "Closed"  So If I use the history I can get better accuracy.

I would like to extract the phrases from ENTRY_TEXT: "Created by" and  "to Resolved" and "to Closed"

This was I can use the ENTRY_DATE to do the time difference between them.  Unless I could do set analysis instead.

Part two I would like an if statement once this is figured out.  Like the date difference between "Created by" to either "to Resolved" or "To Closed".  I issue there is some people resolve tickets and some close them instead.  So check for resolved first then use closed if that doesn't exist.

I know this is a lot to ask.  Anyone out there on a Friday looking for some QlikView points? lol

Thanks!

JOB_TICKET_ID ENTRY_DATE ENTRY_TEXT 165509 6/8/2017 15:16 Updated-Ticket E-Mail sent to Jim 165509 6/8/2017 15:16 Status changed from Resolved to Closed 165509 6/8/2017 15:16 Added Client Note 9854 165509 6/8/2017 13:16 Updated-Ticket E-Mail sent to Jane 165509 6/8/2017 13:16 Status changed from In Progress to Resolved 165509 6/8/2017 13:16 Added new tech note. 165509 6/6/2017 8:44 Updated-Ticket E-Mail sent to Jane 165509 6/6/2017 8:44 Added new tech note. 165509 6/6/2017 8:42 Status changed from Open to In Progress 165509 6/6/2017 8:42 Set 1st-Response Date to 2017-06-06 8:42 165509 6/5/2017 17:21 Alert E-Mail sent to Doug 165509 6/5/2017 15:45 Reassigned-Ticket E-Mail sent to Doug 165509 6/5/2017 15:45 Assigned to Doug from Bill 165509 6/5/2017 15:18 New-Ticket E-Mail sent to Doug and Bill 165509 6/5/2017 15:18 Assigned to Bill 165509 6/5/2017 15:18 Created by Jane 165509 6/5/2017 15:18 Attached Publisher.xlsx Header 1

[sunny_talwar]

No problem

[Anonymous]

Hey Sunny,

I was going to start a new thread but since it has to do with your answer I will ask it here.

I am trying to show the avg time it takes using the expression you gave me:

If(Len(Trim(Max({<ENTRY_TEXT = {'*to Resolved*'}>}ENTRY_DATE))) = 0, Max({<ENTRY_TEXT = {'*to Closed*'}>}ENTRY_DATE), Max({<ENTRY_TEXT = {'*to Resolved*'}>}ENTRY_DATE)) - Max({<ENTRY_TEXT = {'*Created by*'}>}ENTRY_DATE)

Is it possible to wrap an Avg( ) around it? I am not sure how much more you can build on it. 

Ideally too, I would like the Avg and also a condition saying not to include STATUS = Cancelled.

I will be forever grateful again.

Thanks!

[sunny_talwar]

May be like this to add average

Avg(Aggr(

If(Len(Trim(Max({<ENTRY_TEXT = {'*to Resolved*'}>} ENTRY_DATE))) = 0, Max({<ENTRY_TEXT = {'*to Closed*'}>} ENTRY_DATE), Max({<ENTRY_TEXT = {'*to Resolved*'}>} ENTRY_DATE)) - Max({<ENTRY_TEXT = {'*Created by*'}>} ENTRY_DATE)

, AggregatingDimensions))

To exclude Status = Cancelled

Avg({<ENTRY_TEXT = {'*to Resolved*'}, STATUS -= {'Cancelled'}>}Aggr(

If(Len(Trim(Max({<ENTRY_TEXT = {'*to Resolved*'}, STATUS -= {'Cancelled'}>} ENTRY_DATE))) = 0, Max({<ENTRY_TEXT = {'*to Closed*'}, STATUS -= {'Cancelled'}>} ENTRY_DATE), Max({<ENTRY_TEXT = {'*to Resolved*'}, STATUS -= {'Cancelled'}>} ENTRY_DATE)) - Max({<ENTRY_TEXT = {'*Created by*'}, STATUS -= {'Cancelled'}>} ENTRY_DATE)

, AggregatingDimensions))

[Anonymous]

Yeap that's it!