How to bucket Field Values with ApplyMaps

[Anonymous]

I need to achieve the following:

I have productID that have PMT Code in Vendors Table eg (101). These codes are regrouped to another code(1001). Then I am bringing another field -'Ship_Code' by a left join on Regrouped PMT code. I have the following script:

Trans:

LOAD ProductID,

     Cashier,

     DepartmentID,

     VendorID,

FROM Excel;

Inline_table:

Mapping

LOAD * INLINE [

    old, new

    101, 1001

    102, 1001

    103, 1001

    104, 1001

    105, 1001

    106, 1002

    107, 1002

    108, 1002

    109, 1002

    110, 1002

];

Vendor:

LOAD ProductID,

     VendorID as VID,

     [Vendor Name],

     [PMT CODE],

     ApplyMap('Inline_table','[PMT CODE]','Unknown') as [PMT CODE1]

FROM Excel;

Shipment:

Left Join(Vendor)

load [PMT CODE] as [PMT CODE1],

     Ship_code

FROM Excel;

Basically, I need to regroup or reclassify old pmt codes for each Product ID into New code(I am doing this by Inline Table)

ProductID	VendorID	Vendor Name	PMT CODE
XYZ001	1	ABC Inc	101
XYZ002	1	ABC Inc	102
XYZ003	1	ABC Inc	103
XYZ004	2	XYZ Inc	104
XYZ005	2	XYZ Inc	105
XYZ006	3	KLM Inc	106
XYZ007	3	KLM Inc	107
XYZ008	3	KLM Inc	108
XYZ009	3	KLM Inc	109
XYZ010	3	KLM Inc	110

these PMT CODES with 3 digits 101,102 etc should be bucketed in the 2 below codes 1001 & 1002 as mandated by the inline table. And my data should show the appropriate ship code from the table below. How do I achieve it. Please advise.

Shipcode_table:  

PMT CODE	Ship_code
1001	Free Shipping
1002	Regular Shipping

[swuehl]

John,

does the above script code not work for you? On first glance, it seems to do what you want.

So I am not sure what you are asking for.

[Anonymous]

Swuehl,

it is not brining the ship_code field values as in the pic below.

[swuehl · Accepted solution]

Remove the single quotes around the second argument of the applymap function:

     ApplyMap('Inline_table',[PMT CODE],'Unknown') as [PMT CODE1]

[Anonymous]

That was it. Perfect. Thank you SWUEHL.

[swuehl]

If you want to train your mapping skills and to avoid a left join, you can also try something like this:

CodeShipMap:

MAPPING LOAD

      [PMT CODE] as [PMT CODE1],

     Ship_code

FROM Excel;

Inline_table:

Mapping

LOAD old, ApplyMap('CodeShipMap', new, 'UnknownCodeMap') as new

INLINE [

    old, new

    101, 1001

    102, 1001

    103, 1001

    104, 1001

    105, 1001

    106, 1002

    107, 1002

    108, 1002

    109, 1002

    110, 1002

];

Vendor:

LOAD ProductID,

     VendorID as VID,

     [Vendor Name],

     [PMT CODE],

     ApplyMap('Inline_table','[PMT CODE]','Unknown') as Ship_code

FROM Excel;

[Anonymous]

is it possible to keep the field name unchanged to [PMT CODE]?

[swuehl]

You are talking about the field in the first mapping table?

Yes, you can leave it unchanged, but it doesn't matter, because mapping tables don't exist after reload (not present in your data model).