try in this way,what u had done is correct except that while applying applymap fn u need to give the mapping value like FdproductGrp
Now try in this manner hope ur problem is solved..............
if(substringcount(lower(fdProduct),'bbb')<>0,'group C))) as FdproductGrp'...
fdID as CaseID,
applymap('ProductGroup',FdproductGrp) as fdProductGroup..
If you can think of a pattern that does this, you can replace the mapping load with a formula, but otherwise ...
Say you always check for two characters and each next check is the following character (like 'aa', 'bb', 'cc', ...). Then you would be able to create a formula to do this. But your third check indicates a lack of an apparent pattern.
Perhaps create a table (inline or external) that contains the patterns you like to check on (like 'aa', 'bb', 'cc', ...) and then use that field as a checking mechanism. The rest of your statement seems to be the same anyway.
how about using this instead of applymap?
from the help text:
This function can be used for mapping parts of any expression on a previously loaded mapping table. The mapping is case sensitive and non-recursive. The substrings are mapped from the left to the right. Mapname is the name of a mapping table previously read by a mapping load or a mapping select statement. The name must be enclosed by single straight quotation marks. Expr is the expression whose result should be mapped by substrings.
// Assume the following mapping table:
mapping load * inline [
x, b ] ;
MapSubstring ( 'map1', 'A123' )
MapSubstring ( 'map1', 'baaar' )
MapSubstring ( 'map1', 'xaa1' )
Mapsubstring will actual modify the Product ID - I don't believe that was the intent...
The smarter way of doing it lies outside of QlikView boundaries. In smart systems, product Groups are listed as Attributes for Products and not as a substring of the Product Code... The rest of the "mess" is caused by this fundamental problem...