Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
chitemerere
Specialist
Specialist

Data Cleaning and Transformation

I have created the following mapping table:

Screen Shot 2016-03-23 at 09.29.49.png

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

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

chitemerere
Specialist
Specialist
Author

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

Regards

chitemerere
Specialist
Specialist
Author

Hi Sunny

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

sunny_talwar

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

chitemerere
Specialist
Specialist
Author

Hi Sunny

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

Regards

Chris