topic Re: how to clean data? with attached sample in QlikView

how to clean data? with attached sample

n1ef5ng1 — Tue, 10 Sep 2013 08:05:30 GMT

Hi all,

I would want to 'clean' the data that is found mismatch on 'Passenger' table. TXIT_Bus_Name. TXIT_Bus_Name fields = Bus_Name.

As TXIT_Bus_Name fields does not match the bus_Name due to human error in inputting down the data, there is a need to edit the data in this field in order to generate the Bus_Name that the passenger take.

All the link up is essential as it is somehow the design structure of what is my current database is.

Possible to use mapping which i doubt so as there are multiple fields to consider before changing a bus name such as Month_Year, TXIT_Bus_Name before changing the bus_name. Note that it is important to factor in the Month_Year.

Thanks all

Re: how to clean data? with attached sample

n1ef5ng1 — Tue, 10 Sep 2013 08:08:17 GMT

Passenger ID 1 TXIT Bus_Name OPPER should rename as ROPEE

Passenger ID 2 TXIT_Bus_Name TASF should rename as FAST

SYAF rename as HWFD

FASY rename as FASY

Re: how to clean data? with attached sample

Gysbert_Wassenaar — Tue, 10 Sep 2013 17:11:36 GMT

There's no table that maps wrong values with right values. So what you want is not possible with the data you supplied. You say that OPPER should become ROPEE, but that can't be determined from the data. You need to add a table to your data that contains the mapping. Only then can mapping/applymap be used.

Re: how to clean data? with attached sample

n1ef5ng1 — Wed, 11 Sep 2013 00:58:55 GMT

however, am i right to say that mapping only applies to two column. but for my case there are multiple condition to fulfill before giving a new value