6 Replies Latest reply: May 7, 2014 1:14 PM by Lawrence Cooper RSS

    MAP..USING, Mapping LOAD not working for me

    Lawrence Cooper

      I'm trying to clean my data. I have various different "codes" which mean the same thing and I think I need to create a table to remap the codes to the correct value.


      So I have created the following (this is an example, I have added comments here to explain what I think I'm doing):


      //Create a lookup table. My spreadsheet has 12 columns and I just want to use two from the middle labelled as Code and newCode


      Mapping LOAD Code, newCode FROM [My spreadsheet.xlsx]


      (ooxml, embedded labels, table is HB070514)
      WHERE Include='Y';

      /*        Example structure...

           Include, WSDgrp, Code, newCode, grp2, grp3, ...

           Y,       N,      A1,   A,       abcd, abc, ...

           Y,       N,      A2,   A,       abcd, abc, ...

           N,       N,      B1,   B,       bcde, bcd, ...

           Y,       N,      B2,   B,       bcde, bcd, ...

           Y,       N,      B3,   B,       bcde, bcd, ...


      // I now have a "mapping table" called CodeMap which contains two columns named Code and newCode



      // Turn mapping on
      MAP Code using codeMap;


      // Load data from fact tables created elsewhere, each table has a field called Code.


      LOAD * FROM [Fact table 1.qvd] (qvd);


      Concatenate LOAD * FROM [Fact table 2.qvd] (qvd);


      Concatenate LOAD * FROM [Fact table 3.qvd] (qvd);


      //Turn mapping off

      UNMAP Code;


      So, what I think should have happened by now is the contents of the field "Code" in each of the qvd files have had their values changed to the equivalents in the codeMap table. If record 1 of the qvd file has A2 in the Code field, record 1 of the now resident FactTable contains A in the Code field instead.


      However, pre-viewing the data in the Table Viewer shows that A2 is still in that field.


      Therefore, I'm not doing it right - the mapping load and map..using commands are not doing what I think they're supposed to do, or does map..using not work on qvd files?