Search for values given in existing table and if found YES else NO

[nachiket_shinde]

Hello,

I have list of values in one excel.

I have one straight table with column name 'Values'.

I have to search for excel values in the column name and if found I need to create new column 'Flag' in straight table and put values 'YES' and if not found then 'NO'

Please suggest.

Note that table column has big strings in it, so I have to find out keyword in the string.Keywords could be 2-3 words as well.

[Anil_Babu_Samineni]

You can use

If(SubStringCount(Values, 'Text Search'), 'YES', 'NO')

[nachiket_shinde]

I have multiple values in excel that i need to search in 'value' column of the table.

In you example, what needs to be given at 'Text Search'?

[vishsaggi]

Can you share some sample data coming in that field?

[its_anandrjs]

Use index() function here to find the specific word

Ex:-

LOAD *, if(Index(ColA,'Hello'),1,0) as TextFlag;

LOAD * Inline

[

ColA

dfadasd Hello sds

Hello sd sdsds

dsjdls wlewj Hello

sdsd

dsdafgdg

gfdgf

];

[nachiket_shinde]

Excel sheet has only 1 column 'colour' and  has data

red ,

yellow,

white,

light blue,

dark blue,

with dark green,

without yellow

I have table with one of the column 'color code' with data

1.black

2.with green

3.i have a dark blue jeans

4.any t-shirt without green

5.any top without yellow

so now when I introduce new column flag

it should have 'YES' in 3 and 5

and 'NO' in all others

[rwunderlich]

Take a look at this example to see how you can link together.

Qlikview Cookbook: Indexing Keywords in Text http://qlikviewcookbook.com/recipes/download-info/indexing-keywords-in-text/

stalwar1‌ has also posted some excellent example scripts on how to index/search like this. Perhaps he'll offer one up.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[sunny_talwar]

Thank you rwunderlich‌ for calling out my name . I applied the MapSubString approach to get this done

Color:

Mapping

LOAD Color,

'/Yes\' as Flag;

LOAD * INLINE [

    Color

    red

    yellow

    white

    light blue

    dark blue

    with dark green

    without yellow

];

Data:

LOAD *,

If(Len(Trim(TextBetween(MapSubString('Color', Data), '/', '\'))) = 0, 'No', TextBetween(MapSubString('Color', Data), '/', '\')) as Flag;

LOAD * INLINE [

    Data

    1.black

    2.with green

    3.i have a dark blue jeans

    4.any t-shirt without green

    5.any top without yellow

];

[nachiket_shinde]

Hi Sunny,

  This is partially running in my case.Flag is giving me entire line appended with /Yes. where as I am expecting only YES or NO.

Please note that I have very big paragraphs in Data and 2 letters to 3 words data in mapping table.

[nachiket_shinde]

Just to add,

my data has  / and \ along with all possible special characters