4 Replies Latest reply: Feb 22, 2016 8:46 AM by Bill Markham RSS

    Mapping Load Help

    David Lyness

      Hi,

       

      Quite new to the whole scripting sides of things, basically I have a current mapping system loading from a particular excel sheet, rather than re-work this all as the data is very messy I want to remap this to a different spreadsheet. The code for the original mapping is below:

       

       

      Directory;
      MapL3desctohelpdesk:
      Mapping LOAD
      Mapping as [Case Type],
      [Level 3 Process Name]
      FROM
      [..\Copy of Service_Framework_V19.xlsx]
      (
      ooxml, embedded labels, table is [New ISE]);



      Directory;
      MapL2desctohelpdesk:
      Mapping LOAD
      Mapping as [Case Type],
      [Level 2 Process Name]
      FROM
      [..\Copy of Service_Framework_V19.xlsx]
      (
      ooxml, embedded labels, table is [New ISE]);


      ApplyMap('MapL3desctohelpdesk',[Case Type],'No Process Mapped') as [Level 3 Process Name],
      ApplyMap('MapL2desctohelpdesk',[Case Type],'No Process Mapped') as [Level 2 Process Name],

       

      I now want to do this:

       

      Directory;
      RemapL3desctohelpdesk:
      Mapping LOAD
      [Case Type],
      [Level 3 Process Name]
      FROM
      [..\Remapping Of Cases.xlsx]
      (
      ooxml, embedded labels, table is [Mappings]);



      Directory;
      RemapL2desctohelpdesk:
      Mapping LOAD
      [Case Type],
      [Level 2 Process Name]
      FROM
      [..\Remapping Of Cases.xlsx]
      (
      ooxml, embedded labels, table is [Mappings]);

       

      ApplyMap('RemapL3desctohelpdesk',[Case Type],'No Process Mapped') as [Level 3 Process Name],
      ApplyMap('RemapL2desctohelpdesk',[Case Type],'No Process Mapped') as [Level 2 Process Name],

       

      This doesnt seem to be working, can anyone see what the issue is and give me any pointers/tips to fix this?

        • Re: Mapping Load Help
          Tresesco B

          You haven't shared the entire script that shows the source (which is to be mapped) table. Hope that table has field called [Case Type]. If so, your script should look like:

           

          Load

                    ApplyMap('RemapL3desctohelpdesk',[Case Type],'No Process Mapped') as [Level 3 Process Name],
                    ApplyMap('RemapL2desctohelpdesk',[Case Type],'No Process Mapped') as [Level 2 Process Name]

          From <> ;




          • Re: Mapping Load Help
            Peter Cammaert

            On its own, "it doesn't work" is the least useful statement you can make. There are probably one million reasons that may cause an "it doesn't work" situation.

             

            A good way to escape from this, is to describe what you do get (NULL values for example) and what you should get. And another useful piece of information (if you don't want to share a demo document or source data for your mapping tables) is this: does the script log show that more than 0 records are being loaded in the mapping tables?

             

            If the answer to the last question is "yes", you could try to use the Upper() & Trim() functions during the creation of the mapping index values (in each load, field [Case Type] then becomes Upper(Trim([Case Type])) AS [Case Type]) and in the applymap() call (use Upper(Trim([Case Type])) as second parameter). In your case the mapping tables do get filled with data, but lookups may experience problems because they treat comparisons as case sensitive.

             

            Best,

             

            Peter

              • Re: Mapping Load Help
                Bill Markham

                What I often do when diagnosing Mapping Load / ApplyMap() issues is to temporarily comment out the Mapping to make it just a normal Load and make sure the key field is in the Fact Table.

                 

                Then it acts like an external dimension and I can see what is actually in what was the Mapping Table and check the join is all ok.  When the issue is diagnosed / sorted then I put it back as a normal Mapping Load / ApplyMap() pair.