topic Re: How to implement Field based string scan and segregate data , key wise ? in QlikView

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

shekhar_analyti — Wed, 25 Nov 2020 16:16:04 GMT

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

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

sunny_talwar — Mon, 08 Jan 2018 14:29:28 GMT

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);

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

shekhar_analyti — Mon, 08 Jan 2018 14:46:37 GMT

Thank you Sunny Bhai

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

sunny_talwar — Mon, 08 Jan 2018 15:00:14 GMT

no problem shekar bhai