6 Replies Latest reply: Feb 3, 2011 11:54 AM by Anne Duffy RSS

    Mapping Load

      Hi -- This is my First post !!!

      I have read the posts above and they dont quite answer my question.

      I'm loading an excel doc REPORT 1 into QV, one of the columns , called DATA , has text and numbers. I have a second table in excel , where using and Index formula it searches within the text field for a specific number and if its found returns a value predetermined in the table.

      This is the Excel formula

      IF(A2="","",LOOKUP(9.99E+307,SEARCH(INDEX(REPT("ZZZZ",Table!$B$1:$B$201="")&Table!$B$1:$B$201,0),TEXT(A2,"00000")),Table!$C$1:$C$201)),A2)

      The table looks a little like this

      Column A Column B

      41500002 Green

      41500003 Blue

       

      The REPORT 1 doc is about 20,000 rows long and the table is about 10,000 rows, so,the index formula is time consuming to run.

      I was wondering if there was a "Partial map" function in QV, where we would load REPORT 1 and map the Table to DATA column , IF it CONTAINS Column A.

      Any suggestions would be great

      Thanks

      Anne

       

        • AW:Mapping Load

          Hello Anne,

          when I understand you right, you read 20.000 times from an excel-file for mapping. This is really time-consuming. My suggestion is to load the map-table (lets call it MyMap) before the data table (REPORT 1)and then do the lookup against the resident table MyMap. After that use drop table MyMap; to get rid of it.

          HtH, Roland

          • Mapping Load
            Vlad Gutkovsky

            Anne,

            Welcome to QlikCommunity! If I understand your question correctly, you want to search inside a field and apply a map if the field contains a substring? There is a function in QV called substringcount(text,substring) that can probably help here. Looking at your Excel logic, I suspect it will be a little complicated in your case so I will leave it to you to write the actual code, but the general syntax would be something like:

            LOAD
            applymap('mymap',mappingfield1) as Result
            ;
            LOAD
            if(substringcount(field1,searchstring)>0,searchstring,null()) as mappingfield1
            RESIDENT mydata;

              • Mapping Load

                Hi Vlad,

                Thats correct , I'm looking to look inside a text feild to see if it contains a number from my mapping in column 1.

                I have been trying the above, but for some reason I cant get my head around it !! ( maybe I need stronger coffee!!)

                Can I run it by you to ensure I have an understanding ?

                My Doc with text field I want to search is called REPORT , the Text field column is called OPICS.

                The Table that I'm referencing is TABLE , the look up fields is KEY and the result is LABEL.

                I have loaded mapping load of table as below

                 

                Mapping

                LOAD Key,

                 

                LOAD Key,

                LOAD

                Key,

                Key,

                 

                Label
                FROM
                [S:\Business Support\Qlikview\Preview\Table.xls]


                Then loaded as normal my Report, - there are 2 other regular mapping on this and they're AOK - see below

                 

                LOAD Branch,
                [Deal No],
                [Rate Code],
                [Posting Date],
                Date,
                Currency,
                Amount,
                Rate,
                Basis,
                Opics,
                Code,
                Type,
                [Branch code],
                ApplyMap ('ratm', [Rate Code]) as [Structured or Unstructured],
                ApplyMap ('branchId',[Branch code]) as [Opics Team]
                FROM
                [S:\Business Support\Qlikview\Preview\Report loads\Master.xls]


                Beneath this I have taken your suggestion ( I think !!) and have amended it to my data. I have tried a few different combinations but keep getting error messages on Load . I was trying to run map from 'numm' to look at Opics feild to cross reference this with the key and any matches come back as BO.

                 

                LOAD
                applymap('numm', Opics) as Result,
                if(substringcount(Opics,searchstring)>0,searchstring,null()) as BO
                FROM
                [S:\Business Support\Qlikview\Preview\Report loads\Master.xls]


                 

                Can you please help as its driving me crazy ?

                 

                Thanks

                Anne