Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You can use
If(SubStringCount(Values, 'Text Search'), 'YES', 'NO')
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'?
Can you share some sample data coming in that field?
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
];
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
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
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
];
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.
Just to add,
my data has / and \ along with all possible special characters