Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
Any way of doing text analysis on the unstructured text in Qlikview?
Maybe the correlation of certain words for example.
Hi Sunny
Able to help how can i implement it in my sample qlikview file as attached?
Thanks
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
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
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;
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
Hi Macro
I have no success in amending it correctly.
anyone else can help pls?
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
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;
Do you get any error while reload, Would you share updated QVW with us
Hi
Pls refer to my first post updated with new qvw file