topic unstructured text analysis in QlikView

unstructured text analysis

Wed, 25 Nov 2020 16:16:04 GMT

Hi there,

Any way of doing text analysis on the unstructured text in Qlikview?

Maybe the correlation of certain words for example.

Re: unstructured text analysis

viveksingh — Tue, 04 Apr 2017 05:30:29 GMT

Hi,

Can you elaborate your question?

what analysis you want to so?

do you want to load this into a table?

Re: unstructured text analysis

Anil_Babu_Samineni — Tue, 04 Apr 2017 05:31:14 GMT

You want to exclude special and number's or what?

May be this =Text(PurgeChar([WO Description],'0123456789'))

If not, I would high request you to create sample lines word should be 4/5 and then let us know output which you required

Re: unstructured text analysis

Tue, 04 Apr 2017 05:36:40 GMT

Hi all,

Maybe when i search certain key word like "EEV" in the WO Description

A table will show me what are the most common words will appear in the WO Description related to "EEV".

Re: unstructured text analysis

Anil_Babu_Samineni — Tue, 04 Apr 2017 05:49:44 GMT

It will work, Try with it by help of wold card search ~EEV~

Re: unstructured text analysis

Tue, 04 Apr 2017 06:10:12 GMT

I did but i have tons of text in such huge unstructured data

what is the best way to do analysis the text?

My idea is the most common related words match "EEV"

Thanks

Re: unstructured text analysis

Anil_Babu_Samineni — Tue, 04 Apr 2017 06:17:01 GMT

this may helps to you.

The way is - look into the matching functions WildMatch() in particular to pick out the similar kind of words and phrases.

And This is the case, you can work and go for installation of extension - May be Word Cloud Extension

Re: unstructured text analysis

Tue, 04 Apr 2017 08:39:11 GMT

where the cloudword.qar file to unzip and put into which qlikview location/folder?

Re: unstructured text analysis

sunny_talwar — Tue, 04 Apr 2017 08:43:00 GMT

May be look at marcowedel‌'s response here:

Re: How to identify recurrent terms in text fields?

Re: unstructured text analysis

Anil_Babu_Samineni — Tue, 04 Apr 2017 08:47:52 GMT

FYI

QlikTip #40: Installing/Deploying QlikView Extensions

Re: unstructured text analysis

Tue, 04 Apr 2017 08:59:59 GMT

Hi Sunny

Able to help how can i implement it in my sample qlikview file as attached?

Thanks

Re: unstructured text analysis

MarcoWedel — Tue, 04 Apr 2017 20:51:26 GMT

Hi,

maybe like this:

WO_with_Job_codes_table:
LOAD AutoNumber([WO Description]) as ID,
    [WO Description]
From WO_with_Job_codes_table.qvd (qvd);

tabWordTuples: 
LOAD Distinct 
    *, 
    SubStringCount(WordTuple,' ')+1 as WordCount; 
LOAD ID, 
    WordStart, 
    Trim(PurgeChar(WordTuple,'.,?')) as WordTuple 
Where Len(Trim(WordTuple)); 
LOAD ID, 
    Div(IterNo()-1,3)+1 as WordStart, 
    Mid([WO Description],Index(' '&[WO Description],' ',Div(IterNo()-1,3)+1),Index(' '&[WO Description]&'  ',' ',Div(IterNo()-1,3)+Mod(IterNo()-1,3)+2)-Index(' '&[WO Description],' ',Div(IterNo()-1,3)+1)-1) as WordTuple
Resident WO_with_Job_codes_table 
While IterNo()<=(SubStringCount([WO Description],' ')+1)*3;

hope this helps

regards

Marco

Re: unstructured text analysis

MarcoWedel — Tue, 04 Apr 2017 21:55:04 GMT

in order to not only compare consecutive word tuples you might use some approach like this:

mapCharToSpace:
Mapping LOAD char, ' ' Inline [
  char
  ","
  .
  (
  )
  :
  ;
  /
];

WO_with_Job_codes_table:
LOAD AutoNumber([WO Description]) as ID,
    [WO Description]
From WO_with_Job_codes_table.qvd (qvd);

tabWord:
LOAD ID,
    Word
Where FindOneOf(Left(Word,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ');
LOAD ID,
    SubField(MapSubString('mapCharToSpace',[WO Description]),' ') as Word
Resident WO_with_Job_codes_table;

tabWord2:
LOAD ID,
    Word as Word2
Resident tabWord;

hope this helps

regards

Marco

Re: unstructured text analysis

Wed, 05 Apr 2017 01:07:46 GMT

Hi,

What to do with the QVD file? Do i need to create one?

tried to load the script and got the error

this is my qvw file script:

LOAD [WO No],

[Cntr ID],

[Cntr No],

[WO Created],

[Op Code],

[WO Description],

[Job code],

[Job description],

[Sub Code],

[Sub Code Description],

[PDF PATH],

Year([WO Created]) as Year,

Day([WO Created]) as Day,

Month([WO Created]) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table)

mapCharToSpace:

Mapping LOAD char, ' ' Inline [

char

","

(

)

;

];

WO_with_Job_codes_table:

LOAD AutoNumber([WO Description]) as ID,

[WO Description]

From WO_with_Job_codes_table.qvd (qvd);

tabWord:

LOAD ID,

Word

Where FindOneOf(Left(Word,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ');

LOAD ID,

SubField(MapSubString('mapCharToSpace',[WO Description]),' ') as Word

Resident WO_with_Job_codes_table;

tabWord2:

LOAD ID,

Word as Word2

Resident tabWord;

Re: unstructured text analysis

MarcoWedel — Wed, 05 Apr 2017 21:36:25 GMT

You try to load from a non existing qvd file I tested with.

Omit the qvd load and integrate the ID field into your initial load from "WO with Job codes table.xlsx" or use one of the already existing ID fields instead.

regards

Marco

Re: unstructured text analysis

Thu, 06 Apr 2017 00:41:04 GMT

Hi Macro

I have no success in amending it correctly.

anyone else can help pls?

Re: unstructured text analysis

Anil_Babu_Samineni — Thu, 06 Apr 2017 01:04:54 GMT

When you talk about not working, You may describe more where this is not working for you. I am sure, Marco solutions works to you. Would you share more on this your expectation

Re: unstructured text analysis

Thu, 06 Apr 2017 01:08:18 GMT

Hi Anil,

I mean trying to amend the script as Macro mentioned.

But i cant figure out how to do it.

LOAD [WO No],

[Cntr ID],

[Cntr No],

[WO Created],

[Op Code],

[WO Description],

[Job code],

[Job description],

[Sub Code],

[Sub Code Description],

[PDF PATH],

Year([WO Created]) as Year,

Day([WO Created]) as Day,

Month([WO Created]) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table)

mapCharToSpace:

Mapping LOAD char, ' ' Inline [

char

","

(

)

;

];

WO_with_Job_codes_table:

LOAD AutoNumber([WO Description]) as ID,

[WO Description]

From WO_with_Job_codes_table.qvd (qvd);

tabWord:

LOAD ID,

Word

Where FindOneOf(Left(Word,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ');

LOAD ID,

SubField(MapSubString('mapCharToSpace',[WO Description]),' ') as Word

Resident WO_with_Job_codes_table;

tabWord2:

LOAD ID,

Word as Word2

Resident tabWord;

Re: unstructured text analysis

Anil_Babu_Samineni — Thu, 06 Apr 2017 01:14:24 GMT

Do you get any error while reload, Would you share updated QVW with us

Re: unstructured text analysis

Thu, 06 Apr 2017 01:24:49 GMT

Pls refer to my first post updated with new qvw file

https://community.qlik.com/message/1244265#1244265