Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

1 Reply
MVP
MVP

Re: How to correct data after load?

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

Community Browser