topic Re: Data Cleaning and Transformation in QlikView

Data Cleaning and Transformation

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have created the following mapping table:

I am cleaning the 'MANUFACTURER' field and replacing it with a cleaned field 'MANUFACTURERNEW'. I will use the ApplyMap function to do the cleaning.

Based on the above table, i also need to create new fields namely 'MCAZAppType' and 'ManufacturerStatus' which are arrived at by inspecting the 'Manufacturer' name. Can this be done at the ApplyMap stage when cleaning the 'Manufacturer' field and if so how?

Regards.

Chris

Re: Data Cleaning and Transformation

sunny_talwar — Wed, 23 Mar 2016 07:54:46 GMT

Yes you can, there are couple of options.

1) Create three different Mapping tables one each for the MANUFACTURERNEW, MCAZAppType, and ManufacturerStatus or

2) create one mapping table with concatenating the three columns into one

MAPPINGTABLE:

MAPPING

LOAD MANUFACTURE,

MANUFACTURERNEW & '|' & MCAZAppType & '|' & ManufacturerStatus

FROM...

and then when you are mapping it use the subfield function to extract each one of them, for instance for MANUFACTURERNEW you can do this:

SubField(ApplyMap('MAPPINGTABLE', MANUFACTURER), '|', 1) as MANUFACTURERNEW,

...

3) You always have the option to do a left join as well.

Re: Data Cleaning and Transformation

Anonymous — Wed, 23 Mar 2016 08:05:29 GMT

Many thanks, Sunny, i will try it out and revert.

Regards

Re: Data Cleaning and Transformation

Anonymous — Wed, 23 Mar 2016 09:18:48 GMT

Hi Sunny

Do you apply the SubField to all fields or just the derived fields?

Re: Data Cleaning and Transformation

sunny_talwar — Wed, 23 Mar 2016 09:21:14 GMT

Just the three new fields that you are adding. Everything else stays the same.

LOAD *,

SubField(ApplyMap('MAPPINGTABLE', MANUFACTURER), '|', 1) as MANUFACTURERNEW,

SubField(ApplyMap('MAPPINGTABLE', MANUFACTURER), '|', 2) as MCAZAppType,

SubField(ApplyMap('MAPPINGTABLE', MANUFACTURER), '|', 3) as ManufacturerStatus

Resident XYZ...

Re: Data Cleaning and Transformation

Anonymous — Wed, 23 Mar 2016 10:05:38 GMT

Hi Sunny

Many thanks, i have applied it to my QV application and it works for me.

Regards

Chris