How to implement Field based string scan and segregate data , key wise ?

[shekhar_analyti]

Hi All

How to implement Field based string scan and segregate data , key wise ?

Table 1

Words_to_be_Scanned

theft

scare

appreciate

fraud

Risk

financial

keen

Table 2

SSN	Date_of_Visit	Comment	Call_Time
123	12/12/2017	It was an online fraud and my financials are at dire risk	2:00 AM
123	12/13/2017	Beware of smiling villains	3:00 AM
123	12/14/2017	I appreciate welfare steps and keen to see those	4:00 AM
123	12/15/2017	This is not done	5:00 AM
124	12/16/2017	This is a risk and full of blind guesses	6:00 AM
125	12/17/2017	I am having keen desire to held it back	7:00 AM
126	12/18/2017	its not even fraud prima facie	8:00 AM
127	12/19/2017	he did not scare me at all	9:00 AM

My requirement is that , every word from first table to be searched in comment field of second table and if found then stored

and plot chart :  number of words from first table used per SSN wise .

Thanks & Regards

Shekar

[sunny_talwar · Accepted solution]

Something like this?

Script

MappingTable:

Mapping

LOAD Words_to_be_Scanned,

'/' & Words_to_be_Scanned & '\'

FROM

[..\..\Downloads\scanning.xlsx]

(ooxml, embedded labels, table is Keywords);

Fact:

LOAD SSN,

     Date_of_Visit,

     Comment,

     Call_Time,

     SubField(SubField(Keywords, '\'), '/') as Keywords;

LOAD SSN,

     Date_of_Visit,

     Comment,

     Call_Time,

     MapSubString('MappingTable', Comment) as Keywords

FROM

[..\..\Downloads\scanning.xlsx]

(ooxml, embedded labels, table is Data);

Inner Join (Fact)

LOAD Words_to_be_Scanned as Keywords

FROM

[..\..\Downloads\scanning.xlsx]

(ooxml, embedded labels, table is Keywords);

[shekhar_analyti]

Thank you Sunny Bhai

[sunny_talwar]

no problem shekar bhai