Check for certain words in Excel

[Anonymous]

Hello qlikers,

I'm loading a .qvd file. In the .qvd file there are sentences. I want to check the sentences, if there is a specific word.

I'm checking the sentences with:

SubStringCount(Sentences,'Apple'))) as [Count of Apples]

I have written all the words in an excel file down: 

I don't want to write four times the same thing with others word, for example:

SubStringCount(Sentences,'Apple'))) as [Count of Apples],

SubStringCount(Sentences,'Banana'))) as [Count of Bananas],

....

_______________________

My question is now: How can I integrate the excel file, so that i don't have to write the same ? 

Thank you in advance. 

[StarinieriG]

Hi,

try something like this: (instead of inline, you will use your excel)

NoConcatenate
WORDS:
LOAD * Inline
[Words,Count
Apple,Apples
Banana,Bananas
Grapes,Grapes
];

For i = 0 to NoOfRows('WORDS')-1

LET Word = Peek('Words',$(i),'WORDS');
LET Count = Peek('Count',$(i),'WORDS');

Left Join (SENTENCES)
LOAD
Sentences,
SubStringCount(Sentences,'$(Word)') as [Count of $(Count)]
Resident SENTENCES;

NEXT

DROP Table WORDS;