topic Re: Look up wordings in cell and categorize it accordingly in App Development

Look up wordings in cell and categorize it accordingly

kelvinwhwong-259 — Fri, 02 Apr 2021 07:45:38 GMT

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!

Re: Look up wordings in cell and categorize it accordingly

rwunderlich — Thu, 24 Jan 2019 01:41:22 GMT

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

Re: Look up wordings in cell and categorize it accordingly

kelvinwhwong-259 — Sat, 09 Feb 2019 03:48:22 GMT

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

Re: Look up wordings in cell and categorize it accordingly

rwunderlich — Mon, 11 Feb 2019 17:10:23 GMT

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