Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Aryasmank
Contributor III
Contributor III

How to extract Adjectives from the long text in Qlik Sense

Hi all,

I have a xls file containing the different adjective and I have a text dataset containing long text.

I want to extract the adjective from this dataset for some analysis like which adjective is used most, etc.

is there anything by which I can extract the adjective using the first xls file( contains different adjective) so that I can count them line by line.

I will be really helpful .

Thanks in advance

Regards,

M

2 Solutions

Accepted Solutions
NitinK7
Specialist
Specialist

 

Kindly refer to the below link

Adjective:

LOAD
Lower(Adjective) as Adjective
FROM [lib://desk/New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Sheet1);

Example:
LOAD
S.NO,
Lower("Text") as Text
FROM [lib://desk/Example.xlsx]
(ooxml, embedded labels, table is Text);

Let vCount=NoOfRows('Example');
Let vadj=NoOfRows('Adjective');

final:
Load '' as TempField AutoGenerate 0;


For i=1 to $(vCount);

countword:
load
S.NO,
Text,
len(KeepChar(Text,' ')) as NumberOfWords
Resident Example
where Match(S.NO,$(i));

Let NumberOfWords= peek('NumberOfWords');
drop Table countword;

For j=0 to $(vadj)-1;

For k=1 to $(NumberOfWords);

Concatenate(final)
load
RowNo() as Num,
S.NO,
Text,
SubStringCount( SubField(Text,' ',$(k)),peek(Adjective,$(j),'Adjective') ) as countofadj
Resident Example
where Match(S.NO,$(i));

Next k

Next j

next i

drop Table Example;

 

Also please check the attached .qvf file

 

View solution in original post

NitinK7
Specialist
Specialist

hi,

1. len(KeepChar(Text,' ')) as NumberOfWords

- count the number of the word in a specific sentence based on space eg if space count 2 the number of the word is 3

2.  SubStringCount( SubField(Text,' ',$(k)),peek(Adjective,$(j),'Adjective') ) as countofadj

SubStringCount return 1 if two parameter match with other , in our case SubField(Text,' ',$(k)) (each word in sentence)  match with all Adjective peek(Adjective,$(j),'Adjective') , if both are match then return 1

3. count({<countofadj={1}>}countofadj) 

 -count the total adjective in a sentence  (count of countofadj )where SubStringCount return 1

 

4. how should I also print the name of that adjective.

add red highlighted code in the below script and add one more measure in your table (frontend/visualization)

concat({<countofadj={1}>}Adjective,',')

 

Concatenate(final)
load
RowNo() as Num,
S.NO,
Text,
SubStringCount( SubField(Text,' ',$(k)),peek(Adjective,$(j),'Adjective') ) as countofadj,

SubField(Text,' ',$(k)) as Adjective
Resident Example
where Match(S.NO,$(i));

 

please find attached .qvf file

 

 

View solution in original post

5 Replies
Patricia_Silva
Specialist II
Specialist II

Hello @Aryasmank, thank you for posting to our Qlik Community! Could you please share an example of an xls file so I can have a look and perform some testing to check if that is something we can achieve?

Help users find answers! Don't forget to mark a correct resolution 🙂
NitinK7
Specialist
Specialist

 

Kindly refer to the below link

Adjective:

LOAD
Lower(Adjective) as Adjective
FROM [lib://desk/New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Sheet1);

Example:
LOAD
S.NO,
Lower("Text") as Text
FROM [lib://desk/Example.xlsx]
(ooxml, embedded labels, table is Text);

Let vCount=NoOfRows('Example');
Let vadj=NoOfRows('Adjective');

final:
Load '' as TempField AutoGenerate 0;


For i=1 to $(vCount);

countword:
load
S.NO,
Text,
len(KeepChar(Text,' ')) as NumberOfWords
Resident Example
where Match(S.NO,$(i));

Let NumberOfWords= peek('NumberOfWords');
drop Table countword;

For j=0 to $(vadj)-1;

For k=1 to $(NumberOfWords);

Concatenate(final)
load
RowNo() as Num,
S.NO,
Text,
SubStringCount( SubField(Text,' ',$(k)),peek(Adjective,$(j),'Adjective') ) as countofadj
Resident Example
where Match(S.NO,$(i));

Next k

Next j

next i

drop Table Example;

 

Also please check the attached .qvf file

 

Aryasmank
Contributor III
Contributor III
Author

Thanks, @NitinK7  for the solution,

but one more doubt how should I also print the name of that adjective?

and can you please explain these parts:

1:  len(KeepChar(Text,' ')) as NumberOfWords

2. SubStringCount( SubField(Text,' ',$(k)),peek(Adjective,$(j),'Adjective') ) as countofadj

 and why you use count({<countofadj={1}>}countofadj) in the table. 

 

Thanks

M

 

NitinK7
Specialist
Specialist

hi,

1. len(KeepChar(Text,' ')) as NumberOfWords

- count the number of the word in a specific sentence based on space eg if space count 2 the number of the word is 3

2.  SubStringCount( SubField(Text,' ',$(k)),peek(Adjective,$(j),'Adjective') ) as countofadj

SubStringCount return 1 if two parameter match with other , in our case SubField(Text,' ',$(k)) (each word in sentence)  match with all Adjective peek(Adjective,$(j),'Adjective') , if both are match then return 1

3. count({<countofadj={1}>}countofadj) 

 -count the total adjective in a sentence  (count of countofadj )where SubStringCount return 1

 

4. how should I also print the name of that adjective.

add red highlighted code in the below script and add one more measure in your table (frontend/visualization)

concat({<countofadj={1}>}Adjective,',')

 

Concatenate(final)
load
RowNo() as Num,
S.NO,
Text,
SubStringCount( SubField(Text,' ',$(k)),peek(Adjective,$(j),'Adjective') ) as countofadj,

SubField(Text,' ',$(k)) as Adjective
Resident Example
where Match(S.NO,$(i));

 

please find attached .qvf file

 

 

Aryasmank
Contributor III
Contributor III
Author

@NitinK7  Thanks for clarification.