New to Qlik Sense

[Aryasmank]

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

[Patricia_Silva]

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?

[NitinK7 · Accepted solution]

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]

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 · Accepted solution]

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]

@NitinK7  Thanks for clarification.