6 Replies Latest reply: Apr 17, 2010 9:29 PM by Rob Wunderlich RSS

    Mapping load with IF statements

      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
          Shivarama krishna K

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

          • Mapping load with IF statements
            MManders

            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
              DANIEL CHOTZEN

              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

                  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
                  Rob Wunderlich

                  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