Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
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.
Many thanks, Sunny, i will try it out and revert.
Regards
Hi Sunny
Do you apply the SubField to all fields or just the derived fields?
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...
Hi Sunny
Many thanks, i have applied it to my QV application and it works for me.
Regards
Chris