18 Replies Latest reply: Apr 1, 2015 4:26 AM by Stuart Frew RSS

    Create mapping table using substrings

    Stuart Frew

      Hi,

       

      I have data in the following formats:-

       

      There are roughly 50 ATB codes

      ATB CodeDescriptionCompatible CIB Codes
      1Central0111-6423, 7012-7018, 6603
      2Local0111-6423, 7012-7018

       

      +

       

      There are roughly 500 CIB Codes

      CIB CodesDesc
      0111Desc 1
      0112Desc 2
      0123Desc 3
      0124Desc 4

       

      I want to create a row based table of acceptable ATB and CIB combinations that would end up in this format:-

      ATB CodeCIB Codes
      10111
      10112
      10123
      10124

       

      Is this doable using Qlikview? If so what script commands would one use?

       

      Thanks

      Stuart

        • Re: Create mapping table using substrings
          Ramon Covarrubias

          Mapping Functions

          The ApplyMap function is used for mapping any expression to a previously loaded mapping table. The syntax is:

          applymap('mapname', expr [ , defaultexpr ] )

          where:

          mapname is the name of a mapping table that has previously been created through the mapping load or the mapping select statement (see Mapping). Its name must be enclosed by single, straight Quotation Marks in Scripting.

          expr is the expression, the result of which should be mapped.

          defaultexpr is an optional expression which will be used as a default mapping value if the mapping table does not contain a matching value for expr. If no default value is given, the value of expr will be returned as is.

          Examples:

          // Assume the following mapping table:

          map1:

          mapping load * inline [

          x, y

          1, one

          2, two

          3, three ] ;

           

          ApplyMap ('map1', 2 ) returns ' two'

          ApplyMap ('map1', 4 ) returns 4

          ApplyMap ('map1', 5, 'xxx') returns 'xxx'

          ApplyMap ('map1', 1, 'xxx') returns 'one'

          ApplyMap ('map1', 5, null( ) ) returns NULL

          ApplyMap ('map1', 3, null( ) ) returns 'three'

           

          The MapSubstring function is used to map parts of any expression to a previously loaded mapping table. The mapping is case sensitive and non-iterative and substrings are mapped from left to right. The syntax is:

          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 (see Mapping). 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>'

           

           

           

          QlikView 11.20 SR6

          • Re: Create mapping table using substrings
            Avinash R

            Hi Stuart,

             

            If you just need to have data from table to be linked with all the  data from table 2 then concatenate would do, But what the mapping for 2 here? I.e how you identify 1 showed be linked with 0111,0112,0113 etc ?? and even 2 data show be linked with the 0111,0112,0113 etc? if that is the case then simply concatenate or if their is any condition please specify that mapping condition

            • Re: Create mapping table using substrings
              Marcus Malinow

              Try this:

               

               

               

               


              _ATB:
              LOAD ATB,
              [Compatible ATB Codes],
              SubStringCount([Compatible ATB Codes], ',') + 1 as Subfields
              FROM
              [sample data set.xlsx]
              (
              ooxml, embedded labels, table is [ATB Codes]);

              MaxSubfields:
              LOAD
              max(Subfields) as maxSubfields
              RESIDENT _ATB;

              Let vMaxSubfields = peek('maxSubfields', -1);

              DROP TABLE MaxSubfields;

              for n = 1 to vMaxSubfields
              TRACE $(n);

              ATB:
              LOAD
              ATB,
              Subfield([Compatible ATB Codes], ',', $(n)) as CompatibleATBCode,
              $(n) as loop
              RESIDENT _ATB
              WHERE $(n) <= Subfields
              ;

              next n

              DROP TABLE _ATB;

              • Re: Create mapping table using substrings
                wrvs fsbv

                Yes it is possible:


                T1:

                LOAD *  INLINE [

                ATBCode          ,     CompCIBCode

                1, '01-20, 30-39'

                2, '10-15, 20-29'

                ];

                 

                 

                T2:

                Load ATBCode,

                trim(SubField(CompCIBCode,',')) as CompCIBCode1

                Resident T1;

                DROP Table T1;

                 

                 

                T3:

                load ATBCode,

                SubField(CompCIBCode1,'-',1) as Start, subField(CompCIBCode1,'-',2) as End

                Resident T2;

                DROP table T2;

                 

                 

                for i=0 to NoOfRows('T3')

                let vStart=num#(Peek('Start',$(i),'T3'),'#');

                let vEnd=num#(peek('End',$(i),'T3'),'#');

                Let vATBCode=Peek('ATBCode',$(i),'T3');

                 

                 

                  for j= '$(vStart)' to '$(vEnd)'

                 

                  T4:

                  load '$(vATBCode)' as ATBCode,

                  '$(j)' as CIBCode

                  AutoGenerate 1;

                  next;

                next;

                drop Table T3;

                • Re: Create mapping table using substrings
                  Joseph Simmons

                  Hi Stuart,

                   

                  I think the attached should do what you are looking for.

                   

                  Hope that helps

                  Joe

                  • Re: Create mapping table using substrings
                    Ramon Covarrubias

                    Joe's use of interval match is a good idea and it was something I overlooked

                     

                    you can use this if you want to use a for

                     

                    CIB:

                    LOAD NUM(CIB,0000) AS CIB,

                         F2

                    FROM

                    [..\sample data set.xlsx]

                    (ooxml, embedded labels, table is [CIB CODES]);

                     

                     

                     

                     

                     

                     

                    TEMP:

                    LOAD ATB,

                         [Compatible ATB Codes]

                    FROM

                    [..\sample data set.xlsx]

                    (ooxml, embedded labels, table is [ATB Codes]);

                     

                     

                     

                     

                     

                     

                     

                     

                    let vATBValuesCount= FieldValueCount('ATB');

                     

                     

                     

                     

                    for ATBCounter = 1 to $(vATBValuesCount)

                      let vATBCode = FieldValue('Compatible ATB Codes',$(ATBCounter));

                     

                     

                      for vATBCodeCounter = 1 to (SubStringCount(FieldValue('Compatible ATB Codes',$(ATBCounter)),','))+1

                     

                      let vATBValue = SubField('$(vATBCode)',',',$(vATBCodeCounter));

                     

                     

                      if index($(vATBValue), '-') > 0 then

                     

                     

                     

                     

                      for RangerCounter = SubField('$(vATBValue)','-',1) to  SubField('$(vATBValue)','-',2);

                     

                      ATB:

                      load

                      ATB,

                      NUM($(RangerCounter),0000) as CIB

                      Resident TEMP;

                     

                     

                     

                     

                      next

                     

                     

                      ELSE

                      ATB:

                      load

                      ATB,

                         NUM($(RangerCounter),0000) as CIB

                      Resident TEMP;

                      ENDIF

                     

                      NEXT

                     

                     

                     

                     

                    NEXT

                     

                     

                     

                     

                     

                     

                     

                     

                     

                     

                     

                     

                    LEFT JOIN(ATB)

                    LOAD

                    *

                    Resident CIB;

                     

                     

                     

                     

                    DROP TABLES CIB;