Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load script find value from one table as substring

Hi all

If I have an excel file of keywords, e.g.

Keywords:

Load Keyword

from test.xls;

and a results file which contains numerous fields including 2 multiline text fields e.g.

Results:

LOAD ID,

Country,

TextField1,

TextField2

from results.xls;

and I want to find out whether each keyword appears in either TextField1 or TextField2 in my results file, what's the best way to go about this?

i.e. if my keywords table looks like this

'apple'

'orange'

'banana'

and the TextField1 of the first row of my results file is '2 apples and a banana'

and the TextField1 of the second row is 'bike' and Text Field2 is  'an orange'

I would want to flag the first row as containing the keywords 'apple' and 'banana' and the 2nd row as containing the keyword 'orange'

I would like to do this in the load script as my results file contains so many rows and I need to provide stats on how many rows contain each of the keyword.

Any advice?

Many thanks in advance

5 Replies
sunny_talwar

May be like this:

Keywords:

Mapping

LOAD Upper(Keyword) as Keyword,

  '/' & Lower(Keyword) & '\' as Flag;

LOAD * Inline [

Keyword

apple

orange

banana

];

Results:

LOAD *,

  PurgeChar(Replace(KeepChar(MapSubString('Keywords', UPPER(TextField1)), 'abcdefghijklmnopqrstuvwxyz/\'), '\/', ', '), '/\') as Flag1,

  PurgeChar(Replace(KeepChar(MapSubString('Keywords', UPPER(TextField2)), 'abcdefghijklmnopqrstuvwxyz/\'), '\/', ', '), '/\') as Flag2;

LOAD * Inline [

ID, TextField1, TextField2

1, 2 apples and a banana, xyz

2, bike, an orange

];

antoniotiman
Master III
Master III

Hi Karen,

Keywords:
LOAD Keyword,Keyword&' '&Peek(Keyword1) as Keyword1 Inline [
Keyword
apple
orange
banana
];
Let vKeyword = Peek('Keyword1');

Table:
Left Keep
LOAD *,KeepChar(TempKeyword,'$(vKeyword)') as Keyword;
LOAD ID,TextField1,TextField2,
SubField(TextField1&' '&TextField2,' ') as TempKeyword
Inline [
ID,TextField1,TextField2
1,2 apples and a banana,cigar
2,bike,an orange
3,car and 3 apples and 1 banana and 2 oranges,
];

Join
LOAD ID,Concat(Keyword,',') as Flag
Resident
Table
Group by ID;
Drop Field Keyword1,TempKeyword
;

Regards,

Antonio

Not applicable
Author

Thanks so much Sunny, Antonio, for the quick replies!

I've tested both ways:

Sunny, yours works but my real keywords could have spaces, your code is removing the space (i.e. if my keyword is Green Car its showing in the Flag1 field as greencar.  Also, if the keyword is found more than once, it is replicating.  Can I make Flag1 only give me unique values?

Antonio,

Unfortunately the data which could appear in textfields 1 & 2 could be very long, (this data is coming from a form and those fields are large free text fields) and I believe the code above is splitting these fields into its individual words, is that right?  I tried running this against my data and its running upwards of 1 mil rows!

Thank both again for your super quick responses,

Karen

sunny_talwar

Here is a slightly modified code which handles the space and also handles the issue of multiple keywords (but at cost of some performance) in Flag1 field only. Not sure if we need to handle the multiple keywords issue for Flag2 field also

Keywords:

Mapping

LOAD Upper(Keyword) as Keyword,

  '/' & Replace(Lower(Keyword), ' ', '@') & '\' as Flag;

LOAD * Inline [

Keyword

apple

Green Car

orange

banana

];

Results:

LOAD ID,

  TextField1,

  TextField2,

  Flag2,

  Concat(DISTINCT Flag1, ', ') as Flag1

Group By ID, TextField1, TextField2, Flag2;

LOAD ID,

  TextField1,

  TextField2,

  SubField(Flag1, ', ') as Flag1,

  Flag2;

LOAD *,

  Replace(PurgeChar(Replace(KeepChar(MapSubString('Keywords', UPPER(TextField1)), 'abcdefghijklmnopqrstuvwxyz/\@'), '\/', ', '), '/\'), '@', ' ') as Flag1,

  Replace(PurgeChar(Replace(KeepChar(MapSubString('Keywords', UPPER(TextField2)), 'abcdefghijklmnopqrstuvwxyz/\@'), '\/', ', '), '/\'), '@', ' ') as Flag2;

LOAD * Inline [

ID, TextField1, TextField2

1, 2 Green Car and a banana, xyz

2, bike, an orange

3, Green Car Green Car Green Car

];


Capture.PNG

MarcoWedel

Hi,

maybe another solution could be:

QlikCommunity_Thread_235397_Pic5.JPG

QlikCommunity_Thread_235397_Pic3.JPG

QlikCommunity_Thread_235397_Pic4.JPG

QlikCommunity_Thread_235397_Pic2.JPG

QlikCommunity_Thread_235397_Pic1.JPG

QlikCommunity_Thread_235397_Pic6.JPG

Keywords:

LOAD RecNo() as %KeywordID, *

INLINE [

    Keyword

    apple

    orange

    banana

    peach

    grape

];

mapKeywords:

Mapping

LOAD Keyword, '@start@'&%KeywordID&'@end@'

Resident Keywords;

Results:

LOAD RecNo() as ID, * INLINE [

    TextField1, TextField2

    2 apples and a banana, some peaches

    bike, an orange

    apple juice, something else

    nothing, anything

    something different, prunes

    many grapes, other fruits

];

tabLink:

CrossTable (TextField, TextFieldValue)

LOAD * Resident Results;

Left Join (tabLink)

LOAD Distinct

    TextFieldValue,

    TextBetween(MapSubString('mapKeywords', TextFieldValue),'@start@','@end@',IterNo()) as %KeywordID

Resident tabLink

While IterNo()<=SubStringCount(MapSubString('mapKeywords', TextFieldValue),'@start@');

hope this helps

regards

Marco