1 Reply Latest reply: Sep 11, 2011 4:09 AM by Miguel Angel Baeyens de Arce RSS

    How to correct data after load?

      Dear All,

       

      I am a newbie working with qlikview and I have a question for you.

      I am loading data from an excel file extracted from a datawarehouse

      Some of the records are crap in term of value, I would like to load the data than update the crap records with right values

       

      I tried to load an other excel file with the records updated, but it looks like I create duplicate

       

      I tried to load first the right record than the second file but it is not working

       

      I need this to be sure I can improve the quality of the data in Qlikview but without being able to correct the source

       

      Thank you in advance for your help

        • Re: How to correct data after load?
          Miguel Angel Baeyens de Arce

          Hi Bruno,

           

          You can use a mapping load to get from one table the correct values and apply to the second table. If there are a lot of fields, this can be cumbersome... Here you are an example on how this works

           

          RightNamesMap:
          MAPPING LOAD EmpID,
               Name;
          SQL SELECT EmpID, Name
          FROM Employees;
          
          DataWithWrongNamesAndCorrection:
          LOAD EmpID,
               Address,
               Name AS OriginalWrongName,
               ApplyMap('RightNamesMap', EmpID, Name) AS RightName;
          SQL SELECT EmpID, Address, Name
          FROM Employees;
          

           

          The key here is the MAPPING prefix in the first table and the ApplyMap() function in the second. What this will do is to check each value of EmpID on the second table and return the value of "Name" in the first table. If the first table doesn't have the EmpID (meaning the second table has already the correct name) then the second table value of "Name" will be kept (hence the "Name" as the third parameter in the ApplyMap() function.

           

          Hope that makes sense.

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica