Mapping load with IF statements

[#1]

I am using a mapping load on a QVD to add a missing category based on a substring from another field. It soon gets messy.

ProductGroup:
MAPPING LOAD
fdProduct,
if(substringcount(lower(fdProduct),'aa')<>0,'group a',
if(substringcount(lower(fdProduct),'bb')<>0,'group b',
if(substringcount(lower(fdProduct),'bbb')<>0,'group b'...

Main:
LOAD
fdID as CaseID,

applymap('ProductGroup',fdProduct) as fdProductGroup..

I have around 30 different sub strings I need to handle. Even though it works doing as in my example above - is there a more clever way?

[spsrk_84]

Hi,

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..............

ProductGroup:
MAPPING LOAD
fdProduct,
if(substringcount(lower(fdProduct),'aa')<>0,'group a',
if(substringcount(lower(fdProduct),'bb')<>0,'group b',
if(substringcount(lower(fdProduct),'bbb')<>0,'group C))) as FdproductGrp'...

Main:
LOAD
fdID as CaseID,

applymap('ProductGroup',FdproductGrp) as fdProductGroup..

[#3]

Thanks, but I do have it like that - and I know that it works.

I just wondered if there's a smarter way to do it?

[#4]

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.

[wizardo]

how about using this instead of applymap?

from the help text:

mapsubstring( 'mapname', expr )

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.

Examples:

// Assume the following mapping table:

map1:

mapping load * inline [

x, y

1, <one>

aa, XYZ

x, b ] ;

MapSubstring ( 'map1', 'A123' )	returns	' A<one>23'
MapSubstring ( 'map1', 'baaar' )	returns	' bXYZar'
MapSubstring ( 'map1', 'xaa1' )	returns	' bXYZ<one>'

[Oleg_Troyansky]

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...

[rwunderlich]

The Qlikview Cookbook sample named "Mapping with a table using wildcards" shows a way to use a pseudo mapping table that utilizes wildcards. May be just what you need.

-Rob

[trishita]

I have a same kind of problem.I have to map two fields from two different tables which are already joined..I want to do it because if i dont map them properly they are showing me redundant data  even if the tables are joined.please help me..the mapping table should be based on some conditions.There are two kinds of fuel type..

The logic is all the fuel type like DMA to DMC can be classifyed as 2 groups HSDO and LSDO on basis of sulphur content.If Fuel type is RMA TO RMG it can be classified as HSFO OR LSFO if sulphur content is more than 0.01 and less than 0.01 rspectively.

So the two fields sounding correction fuel type and summary fuel type should be joined together on the basis of sounding correction.sulphur content..I want to map them because with conditional logic they are still showing redundant data

I want to implement this in my mapping table...

Could this syntax be like:

MAP1:
MAPPING LOAD
  SURVEYID,
if(WildMatch([Fuel Type], 'DM*') and [Sulphur Content] = '>.01','HSDO',

if(WildMatch([Fuel Type], 'DM*') and [Sulphur Content] = ' <:01','LSDO',

if(WildMatch([Fuel Type], 'RM*') and [Sulphur Content] = '>.01','HSFO',

if(WildMatch([Fuel Type], 'RM*') and [Sulphur Content] = ' <01' ,'ULSFO'))))
   as   NIFuelType

FROM [mo_bun_sur_survey_view.qvd]

(qvd);

Main:
LOAD
   SURVEY_ID,

    applymap('MAP1',NIFuelType, 'Unknown') as ActualFueltype

FROM

[mo_purch_sounding_correction_view.qvd]

(qvd);

.