unstructured text analysis

[#1]

Hi there,

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

Maybe the correlation of certain words for example.

[#2]

Hi Sunny

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

Thanks

[MarcoWedel]

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

[MarcoWedel]

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

[#5]

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;

[MarcoWedel]

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

[#7]

Hi Macro

I have no success in amending it correctly.

anyone else can help pls?

[Anil_Babu_Samineni]

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

[#9]

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;

[Anil_Babu_Samineni]

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

[#11]

Hi

Pls refer to my first post updated with new qvw file

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