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]

You can use set analysis.... Something like this

{<ENTRY_TYPE = {'*Created by*'}>}

[sunny_talwar]

For second part... may be this

Max({<ENTRY_TYPE = {'*to Resolved*'}>}ENTRY_DATE) - Max({<ENTRY_TYPE = {'*Created by*'}>}ENTRY_DATE)

[sunny_talwar]

Sample image and qvw attached

[Anonymous]

Hey Sunny,

Thanks for the advice.  I was missing the * for selecting the text. 

Here is the type of expression I need.  I realize this wouldn't work but it leads you in the right direction:

ENTRY_DATE {<ENTRY_TEXT={'*to Resolved*', '*to Closed*'}>}  - ENTRY_DATE {<ENTRY_TEXT={'*Created by*'}>}

Can you make this work?

Thanks so much again

[Anonymous]

Whoa you read my mind!

Can you add "to Closed" in there if "to Resolved" doesn't exist?

Max({<ENTRY_TEXT = {'*to Resolved*'}>}ENTRY_DATE) - Max({<ENTRY_TEXT = {'*Created by*'}>}ENTRY_DATE)

[sunny_talwar]

May be this

If(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)

[Anonymous]

That doesn't work.  I believe it's because of the {'*to Resolved*'}>}ENTRY_DATE) = 0

There isn't a 0 if there is no "to Resolved".  It is just blank or null.  What is the definition for that?

[sunny_talwar · Accepted solution]

Try this then

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)

Len(Trim()) checks for blanks/nulls

[Anonymous]

That did it.

Amazing job friend.

Thanks for making my Friday that much better.