Look up wordings in cell and categorize it accordingly

[kelvinwhwong-259]

Dear friends, 

I am required to look up wordings at individual cells in a large database.
I need to label them into three or four groups by look up key words in a cell.

For example, a paragraph listed at different cell on column A
A1:
"221020181042C-21102018ZRHVR-CTB ID/999011/POSN: #1 ENG REMOVAL REASON: #1 ENG FWD EDP FOUND HYD LEAK
TF12/C/NICO U18023431 C20481022104244 C=IRAD"; 

A2:
"ON CHK FND CB FWD CARGO DOOR CONT AT P414 LOC 'F15' TRIPPED"

A3:
"300920180949-29092018AAACF-PQEID/POSN: CREW LOWER REMOVAL REASON: OXYGEN PRESSURE BELOW LIMIT PRE FLT G25FL/A/STEPH U18063177 C20180930094940"

I hope to demonstrate following words at Column B 
B1: "FF" by understanding word "LEAK" demonstrated fault found

B2: "Other" by understanding word "FND" and "TRIP"

B3: "Incoming" by understanding word "PRE-FLT"

Hence, "FF", "Other", "Incoming" are the category and I hope I can add more criteria by looking several words for categorize as "FF" or "Other" etc.

May I ask how can i manipulate such look up action, by upload the database and visualize them?
Can Quik do it in automatic way or Do I need to simplify them before such multiplication?  Thank you very much! 

[rwunderlich · Accepted solution]

Here's a general approach that will find and label individual words as well as multi-word phrases like "PRE FLT". 

Data:
LOAD *, RecNo() as RecId INLINE [
Entry
"221020181042C-21102018ZRHVR-CTB ID/999011/POSN: #1 ENG REMOVAL REASON: #1 ENG FWD EDP FOUND HYD LEAK TF12/C/NICO U18023431 C20481022104244 C=IRAD"
"ON CHK FND CB FWD CARGO DOOR CONT AT P414 LOC 'F15' TRIPPED"
"300920180949-29092018AAACF-PQEID/POSN: CREW LOWER REMOVAL REASON: OXYGEN PRESSURE BELOW LIMIT PRE FLT G25FL/A/STEPH U18063177 C20180930094940"
]
;

KeywordMap:
MAPPING
LOAD Keyword, chr(01)&chr(02) & Category & chr(02) & chr(01) INLINE [
Keyword, Category
LEAK, FF
FND, Other
TRIP, Other
PRE FLT, Incoming
]
;

Categories:
LOAD Distinct
*
Where len(Category) > 0
;
LOAD
RecId,
TextBetween(mapped, chr(02), chr(02)) as Category
;
LOAD
RowNo() as Rowno,
RecId,
SubField(mapped, chr(01)) as mapped
;
LOAD
RecId,
MapSubString('KeywordMap', Entry) as mapped
Resident Data
;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[kelvinwhwong-259]

Dear Sir, 

May I know how can I load these programme message to the App?
Actually I would to know if Quik is able identify data set from whole column and generate indicators with a new column next to that, thank you!

---

@rwunderlich wrote:

Here's a general approach that will find and label individual words as well as multi-word phrases like "PRE FLT". 

Data:
LOAD *, RecNo() as RecId INLINE [
Entry
"221020181042C-21102018ZRHVR-CTB ID/999011/POSN: #1 ENG REMOVAL REASON: #1 ENG FWD EDP FOUND HYD LEAK TF12/C/NICO U18023431 C20481022104244 C=IRAD"
"ON CHK FND CB FWD CARGO DOOR CONT AT P414 LOC 'F15' TRIPPED"
"300920180949-29092018AAACF-PQEID/POSN: CREW LOWER REMOVAL REASON: OXYGEN PRESSURE BELOW LIMIT PRE FLT G25FL/A/STEPH U18063177 C20180930094940"
]
;

KeywordMap:
MAPPING
LOAD Keyword, chr(01)&chr(02) & Category & chr(02) & chr(01) INLINE [
Keyword, Category
LEAK, FF
FND, Other
TRIP, Other
PRE FLT, Incoming
]
;

Categories:
LOAD Distinct
*
Where len(Category) > 0
;
LOAD
RecId,
TextBetween(mapped, chr(02), chr(02)) as Category
;
LOAD
RowNo() as Rowno,
RecId,
SubField(mapped, chr(01)) as mapped
;
LOAD
RecId,
MapSubString('KeywordMap', Entry) as mapped
Resident Data
;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

---

[rwunderlich]

My example showed loading data Inline purely as demonstration. Your data probably exists in a database table or an excel file and you will load it from there.  If you have further questions, please indicate what is your data source and provide the load statement you use to load it if possible. 

-Rob