Data Cleaning and Transformation

[Anonymous]

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

[sunny_talwar]

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.

[Anonymous]

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

Regards

[Anonymous]

Hi Sunny

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

[sunny_talwar · Accepted solution]

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...

[Anonymous]

Hi Sunny

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

Regards

Chris