Extracting Keywords from Free Text field to create a secondary list

[Keitaru]

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 Code	Time lapse
INC000000394872	TRO Reason CHNTRO #RG5  1260min #RG7 120min	#RG5	1260min
INC000000394872	TRO Reason CHNTRO #RG5  1260min #RG7 120min	#RG7	120min
INC000000395620	GASTRO #RU1 720mins #RU2 1440mins	#RU1	720mins
INC000000395620	GASTRO #RU1 720mins #RU2 1440mins	#RU2	1440mins
INC000000395662	TRO Reason C&CTRO#RG4 4day	#RG4	4day

Much Appreciated. 

[Keitaru]

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:

Source:

[Keitaru]

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

[Keitaru]

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)