31 Replies Latest reply: Sep 30, 2015 6:15 AM by Alex Berry RSS

    ApplyMap with LOAD INLINE not working

      Hi folks,

       

      Usually I don't struggle with the ApplyMap function, has been working great.  However, when I try to use it with an INLINE lookup such as the example below, it will not map to AAContactNoDesc.  Am I doing something wrong here?

       

      MAP_AACONTACTNO:

       

      MAPPING LOAD * INLINE

      [AAContactNumber, AAContactNoDesc

      '0113 438 0120','Flying to Mars'

      '0330 780 7436','Flying to the Moon'];

       

      LOAD Letter_code,  

           DELIVERY_NNA,  

           ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc,  

           LoadedDate

      FROM [\\abcdef.qvd] (qvd);

       

      Many thanks, Alex

        • Re: ApplyMap with LOAD INLINE not working
          Deepak Tibhe

          Try

           

           

           

          MAP_AACONTACTNO:

           

          MAPPING LOAD * INLINE

          [AAContactNumber, AAContactNoDesc

          '0113 438 0120','Flying to Mars'

          '0330 780 7436','Flying to the Moon'];

           

          LOAD Letter_code,  

               DELIVERY_NNA,  

               ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc,  

               LoadedDate

          FROM [\\abcdef.qvd] (qvd);

          • Re: ApplyMap with LOAD INLINE not working
            Coen Rispens

            I think your mapping is set up wrong, try this:

             

            MAPPING LOAD

            AAContactNumber, AAContactNoDesc

            INLINE [

            AAContactNumber, AAContactNoDesc

            0113 438 0120,Flying to Mars

            0330 780 7436,Flying to the Moon];

             

            and remove the extra ) in ApplyMap('MAP_AACONTACTNO',AAContactNumber)) as AAContactNoDesc,

             

            By the way, good debugging practise is to make use of the third parameter that ApplyMap uses. So instead of

             

            ApplyMap('MAP_AACONTACTNO',AAContactNumber)


            You use


            ApplyMap('MAP_AACONTACTNO',AAContactNumber, 'Unknown') so that anything unmapped will show up and you know you missed something.


            EDIT: Had to add the AAContactNumber, AAContactNoDesc also to the inline part. sorry

              • Re: ApplyMap with LOAD INLINE not working

                Thanks Coen,


                I tried it without the single quote and use the 3rd parameter to bucket the unmapped which is useful thanks.  However, instead of getting all telephone numbers back, I am now getting UNKNOWN for everything.

                 

                ApplyMap('MAP_AACONTACTNO',AAContactNumber,'UNKNOWN') as AAContactNoDesc,

                 

                Is this a data type issue possible?  The join key is clearly the contact no, is QV treating it as text or a number? Should I force it to be text like text(AAContactNumber) to ensure the join/mapping works? or?

                 

                Alex

              • Re: ApplyMap with LOAD INLINE not working
                Kalyan Navuluri

                MAP_AACONTACTNO:

                 

                MAPPING LOAD * INLINE

                [AAContactNumber, AAContactNoDesc

                '0113 438 0120','Flying to Mars'

                '0330 780 7436','Flying to the Moon'];

                 

                LOAD Letter_code,

                    DELIVERY_NNA,

                    ApplyMap('MAP_AACONTACTNO',AAContactNumber)) as AAContactNoDesc,

                    LoadedDate

                FROM [\\abcdef.qvd] (qvd);

                 

                remove extra bracket at end of the apply map

                • Re: ApplyMap with LOAD INLINE not working
                  ahmar ansari

                  Alex ApplyMap() is working with one filed which is having same filed name in both table.

                  in your table

                  MAP_AACONTACTNO:

                   

                  MAPPING LOAD * INLINE

                  [AAContactNumber, AAContactNoDesc

                  '0113 438 0120','Flying to Mars'

                  '0330 780 7436','Flying to the Moon'];

                   

                  LOAD Letter_code,  

                       DELIVERY_NNA,  

                       ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc,  

                       LoadedDate

                  FROM [\\abcdef.qvd] (qvd);

                   

                  try this

                   

                   

                  MAP_AACONTACTNO:

                   

                  MAPPING LOAD * INLINE

                  [AAContactNumber, AAContactNoDesc

                  '0113 438 0120','Flying to Mars'

                  '0330 780 7436','Flying to the Moon'];

                   

                  LOAD Letter_code,  

                       DELIVERY_NNA,  

                       AAContactNumber,

                       ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc,  

                       LoadedDate

                  FROM [\\abcdef.qvd] (qvd);

                  • Re: ApplyMap with LOAD INLINE not working
                    Digvijay Singh

                    Not sure though , Do you have AAContactNumber in [\\abcdef.qvd], if yes than check if values are matching with your inline map table values.

                    • Re: ApplyMap with LOAD INLINE not working
                      sunil kumar

                      is there mapping field  AAContactNumber in the second table if so then it work


                      MAP_AACONTACTNO:

                       

                      MAPPING LOAD * INLINE

                      [AAContactNumber, AAContactNoDesc

                      '0113 438 0120','Flying to Mars'

                      '0330 780 7436','Flying to the Moon'];

                       

                      LOAD Letter_code, 

                           DELIVERY_NNA,

                          AAContactNumber  /*  check the field is there or not */

                           ApplyMap('MAP_AACONTACTNO',AAContactNumber) as AAContactNoDesc, 

                           LoadedDate

                      FROM [\\abcdef.qvd] (qvd);

                      • Re: ApplyMap with LOAD INLINE not working
                        Sasidhar Parupudi

                        May be there are spaces in your source string.. try trimming it, also please check if the source strings match to the strings in the mappping load.

                         

                        MAP_AACONTACTNO:

                        MAPPING LOAD * INLINE

                        [AAContactNumber, AAContactNoDesc

                        '0113 438 0120','Flying to Mars'

                        '0330 780 7436','Flying to the Moon'];

                         

                        LOAD Letter_code,  

                             DELIVERY_NNA,  

                             ApplyMap('MAP_AACONTACTNO',Trim(AAContactNumber)) as AAContactNoDesc,  

                             LoadedDate

                        FROM [\\abcdef.qvd] (qvd);

                         

                        hth

                        Sasi

                        • Re: ApplyMap with LOAD INLINE not working
                          mani ram

                          Hi,

                          This is use full for you. You can try this one.

                           

                          MAP_AACONTACTNO:

                           

                          MAPPING LOAD * INLINE

                          [AAContactNumber, AAContactNoDesc

                          '0113 438 0120','Flying to Mars'

                          '0330 780 7436','Flying to the Moon'];

                           

                          LOAD Letter_code,

                               DELIVERY_NNA, ! !

                               ApplyMap('MAP_AACONTACTNO',Letter_code ) as AAContactNoDesc,

                               LoadedDate

                          FROM [\\abcdef.qvd] (qvd);

                           

                          When the data is same in AAContactNumber and  Letter_code that time u will get proper results.

                          • Re: ApplyMap with LOAD INLINE not working
                            Peter Cammaert

                            What is happening if you prefix all phone numbers with a non-numeric character, like '#'. Both in the INLINE table (or wherever your mapping table data is coming from) and in the second parameter of the Applymap call (e.g. '#' & AAContactNumber)?

                            • Re: ApplyMap with LOAD INLINE not working
                              Gabriel Oluwaseye

                              Hi,

                               

                              In your final table use preceding load as in

                               

                               

                              MAP_AACONTACTNO:

                              MAPPING LOAD * INLINE

                              [AAContactNumber, AAContactNoDesc

                              '0113 438 0120','Flying to Mars'

                              '0330 780 7436','Flying to the Moon'];

                               

                               

                              Final_Table:

                              LOAD

                                  Letter_code,

                                   DELIVERY_NNA,

                                   ApplyMap('MAP_AACONTACTNO',Letter_code ) as AAContactNoDesc,

                                   LoadedDate

                              ;

                              LOAD

                                  Letter_code,

                                   DELIVERY_NNA,

                                  //ApplyMap('MAP_AACONTACTNO',Letter_code ) as AAContactNoDesc,

                                   LoadedDate

                              FROM [\\abcdef.qvd] (qvd);

                              • Re: ApplyMap with LOAD INLINE not working

                                Finally solved this problem.  Thanks to this post from Henric https://community.qlik.com/blogs/qlikviewdesignblog/2015/06/30/data-cleansing where the notion of MapSubstring is suggested as an alternative.


                                The final code looks like this, where the text() function is doing its casting job.


                                MAP_AACONTACTNO:


                                LOAD * INLINE

                                [AAContactNumber, AAContactNoDesc

                                '0113 438 0120','Flying to Mars'

                                '0330 780 7436','Flying to the Moon'];

                                 

                                MAP_AACONTACTNO2:

                                 

                                MAPPING LOAD

                                     text(left(AAContactNumber,14)) as AAContactNumber,

                                     AAContactNoDesc

                                RESIDENT MAP_AACONTACTNO;

                                 

                                LOAD Letter_code,    

                                          DELIVERY_NNA,      

                                          MapSubString('MAP_AACONTACTNO2',text(left(AAContactNumber,14)) as AAContactNoDesc,    

                                          LoadedDate

                                FROM [\\abcdef.qvd] (qvd);

                                 

                                Thanks to all those helping to solve this one, much appreciated, Alex.

                                • Re: ApplyMap with LOAD INLINE not working

                                  Upon further reflection, it seems the source data is actually loading with mixed data types i.e. the telephone numbers are being loaded as both numbers and text.  How is this possible?  Even the same number is being loaded twice, once as a number and once as text.

                                  DataTypes.jpg

                                    • Re: ApplyMap with LOAD INLINE not working
                                      Peter Cammaert

                                      It's like you said in a previous post Re: ApplyMap with LOAD INLINE not working: data is loaded in different formats. When QV has a chance to discover a number in a text string, it will store a numerical value together with the text representation. The numerical value will always have precedence. This is the concept of dual() values which is central to qlikview. There is an easy way to verify this mixup: by default numerical values are right aligned, text strings are left aligned.

                                       

                                      Usually this phenomenon is caused by loading field values from different sources. One source will propose them as text values, another one will make them look like numerical values. Only one problem: if you use them as key values, they will not match. If you use them in a list box, they will not map to the same underlying value (for comparisons).. they will be treated as separate values, although they may look identical (see your screenshot).

                                       

                                      Everywhere you load phone numbers, force them to be treated as text by using the text() function. Text() will store a value as text-only and block the conversion to numerical values.

                                      Or, as I suggested before, prefix all phone numbers with a non-numerical character like '#' when loading them from the source. This will block the interpretation - behind the scenes - by QV.

                                       

                                      Best,

                                       

                                      Peter

                                    • Re: ApplyMap with LOAD INLINE not working

                                      And for those that have been following this post, it seems that the key function that really made a difference was KeepChar.  The data loading into AAContactNumber was formatted like 0800 123 1234, however, the spaces between the numbers were not being seen by QV as spaces.  Therefore, KeepChar filters out all non-numerics to leave a clean text string that could be mapped to a description.

                                       

                                      MapSubString('MAP_AACONTACTNO2',text(keepchar(AAContactNumber,'0123456789'))) as AAContactNoDesc,


                                      For reference, I was obviously just assuming that what appeared to be spaces were spaces and treated as spaces .

                                       

                                      Thanks to KeepChar, I can now successfully map millions of calls to a description.