Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
Keitaru
Creator
Creator

Extracting Keywords from Free Text field to create a secondary list

Hi Qliksense Experts,

Need some help with following:

Recently my teams have been tagging special keywords into their free text field to as way of identifying reasons to why they may have missed their TRO. 

The example looks something like:

INC000000394872

Impact Analysis: Functional Query
Root Cause: xxx code no difference in xxx and xxxx
Resolution:Resolved routing to xxxx
Workaround: NA
Problem ticket : NA
KB Applicable [YES/No] : No

TRO Reason
CHNTRO #RG5 1260min #RG7 120min

INC000000395620

Impact Anaysis: Minimal impact.
Root Cause: Customer received SMS not intended for him.
Resolution/Workaround:
To summarize the findings:
• Customer is not holding any current account
• The source of SMS cannot be determined.

Problem Ticket raised: N.A.
Related to Change: No
Ticket Categorization: User > Business User > Inquiry / Knowledge

GASTRO #RU1 720mins #RU2 1440mins

INC000000395662

Root Cause
Resolution
L3 team to fix

Improvement applicable(Y/N)
No

KB applicable(Y/N)
No

TRO Reason
C&CTRO#RG4 4day

 

Text usually after either TRO Reasons and/or after CHNTRO, GASTRO, C&CTRO, PLTTRO 

Trying to have it look like such:

  TRO Reason CodeTime lapse
INC000000394872TRO Reason
CHNTRO #RG5  1260min #RG7 120min
#RG51260min
INC000000394872TRO Reason
CHNTRO #RG5  1260min #RG7 120min
#RG7 120min
INC000000395620GASTRO #RU1 720mins #RU2 1440mins#RU1720mins
INC000000395620GASTRO #RU1 720mins #RU2 1440mins#RU2 1440mins
INC000000395662TRO Reason
C&CTRO#RG4 4day
#RG44day

 

Much Appreciated. 

 

 

 

Labels (3)
6 Replies
Keitaru
Creator
Creator
Author

How do I use a while or do while loop to create a list with each individual #R code as a single line?

 

"TRO Reason Code":
load Distinct "Incident ID*+",
pick(WildMatch(Resolution,'#RU1','#RU2','#RU3','#RU4','#RU5','#RG1','#RG2','#RG3','#RG4','#RG5','#RG6','#RG7','#RG8','#RG9','#RP1','#RP2','#RP3','#RP4','#RP5','#RP6','#RP7','#RO1'),
'#RU1','#RU2','#RU3','#RU4','#RU5','#RG1','#RG2','#RG3','#RG4','#RG5','#RG6','#RG7','#RG8','#RG9','#RP1','#RP2','#RP3','#RP4','#RP5','#RP6','#RP7','#RO1') as "TRO Reason Code"

Resident "Incident Master";

MayilVahanan

HI @Keitaru 

Try like below

Temp:
LOAD Incident,
Remark,
Mid(Remark, FindOneOf(Remark, '#')) as RCode
FROM
YourSource;

Load *, SubField(RCodeTemp, ' ',1) as [TRO Reason Code], SubField(RCodeTemp, ' ',2) as [Time lapse] Where Len(Trim(RCodeTemp))>0;
Load
Incident, SubField(RCode, '#') as RCodeTemp
Resident Temp;

Screenshot:

MayilVahanan_0-1606710435075.png

Source:

MayilVahanan_1-1606710487219.png

 

Thanks & Regards,
Mayil Vahanan R
Keitaru
Creator
Creator
Author

Hi Mayil,

Thank you for the help could you explain to me exactly how this works as I would love to better understand. How each function is used to call on the the following RCodes.

MayilVahanan

Hi @Keitaru 

Mid(Remark, FindOneOf(Remark, '#')) <-- it takes the character from '#' to end of the string.

FindOneOf() searches a string to find the position of the occurrence of any character from a set of provided characters.

Mid() returns the part of the input string starting at the position of the character

 

 SubField(RCode, '#')  <-- it will separate the words by '#'

Subfield() is used to extract substring components from a parent string field, where the original record fields consist of two or more parts separated by a delimiter

 

Thanks & Regards,
Mayil Vahanan R
Keitaru
Creator
Creator
Author

So i have  just one question

would i be able to search for a string  within free text field with FindOneOf or does this only applies to just a single character usage.

MayilVahanan

Hi @Keitaru 

It will search the character in a string. 

ex: FindOneOf(Country, 'ia')

For India , it will give 4 (i is available in first position)

For Japan, it will give 2 (a is available in second position)

Thanks & Regards,
Mayil Vahanan R