topic Mapping load with IF statements in QlikView

Mapping load with IF statements

Tue, 19 Jan 2010 10:40:54 GMT

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?

Mapping load with IF statements

spsrk_84 — Tue, 19 Jan 2010 13:04:03 GMT

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

Main:
LOAD
fdID as CaseID,

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

Mapping load with IF statements

Tue, 19 Jan 2010 13:13:28 GMT

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?

Mapping load with IF statements

Wed, 20 Jan 2010 19:16:07 GMT

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.

Mapping load with IF statements

wizardo — Sat, 17 Apr 2010 22:48:35 GMT

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

Mapping load with IF statements

Oleg_Troyansky — Sun, 18 Apr 2010 00:26:19 GMT

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

Mapping load with IF statements

rwunderlich — Sun, 18 Apr 2010 01:29:44 GMT

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

Re: Mapping load with IF statements

trishita — Wed, 30 May 2018 11:08:57 GMT

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);