topic How to bucket Field Values with ApplyMaps in QlikView

How to bucket Field Values with ApplyMaps

Anonymous — Tue, 26 May 2015 21:37:24 GMT

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

Re: How to bucket Field Values with ApplyMaps

swuehl — Tue, 26 May 2015 21:46:19 GMT

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.

Re: How to bucket Field Values with ApplyMaps

Anonymous — Tue, 26 May 2015 21:57:25 GMT

Swuehl,

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

Re: How to bucket Field Values with ApplyMaps

swuehl — Tue, 26 May 2015 22:03:36 GMT

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

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

Re: How to bucket Field Values with ApplyMaps

Anonymous — Tue, 26 May 2015 22:12:10 GMT

That was it. Perfect. Thank you SWUEHL.

Re: How to bucket Field Values with ApplyMaps

swuehl — Tue, 26 May 2015 22:23:56 GMT

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;

Re: How to bucket Field Values with ApplyMaps

Anonymous — Tue, 26 May 2015 22:26:42 GMT

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

Re: How to bucket Field Values with ApplyMaps

swuehl — Tue, 26 May 2015 22:32:52 GMT

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