ApplyMap() Vs Lookup() - Material Description

[iluilyas]

Hi Folks,

What am I trying to do is, i want to exclude the purchase line items, that are part of material master.

Like, buyers used to purchase non-stock material repeatedly and over the period, that gets converted as a stock material.

My Tables:

Materials: Material Master (MATRL_ID, MATRL_DESC)

Purchase: Purchases (MR_DTL_MATRL_DESC)

I have used Lookup to see, if the description matches with that of a stock item and it works fine.

if(isnull(Lookup('MATRL_DESC','MATRL_DESC',MR_DTL_MATRL_DESC,'Materials')),0,1) AS MR_DTL_IS_MATRL,

Since ApplyMap is faster than Lookup, i'm trying to achieve the same using below code, but not working:

Materials:

LOAD MATRL_ID, MATRL_CD, MATRL_DESC,

MATRL_GROP_CD, MATRL_GROP_NAME, MATRL_GROP_CATGRY

FROM $(tQvd_Path)Materials_t.qvd (qvd);

MapMatrlDesc:

Mapping Load MATRL_DESC, MATRL_ID

FROM $(tQvd_Path)Materials_t.qvd (qvd);

Purchase:

Load

/*if(isnull(Lookup('MATRL_DESC','MATRL_DESC',MR_DTL_MATRL_DESC,'Materials')),0,1) AS MR_DTL_IS_MATRL,*/

if(ApplyMap('MapMatrlDesc',MR_DTL_MATRL_DESC,0)>0,1,0) AS MR_DTL_IS_MATRL, ...

FROM $(tQvd_Path)MA_MATRL_REQST_t.qvd (qvd);

I even added pre and post concatenations to make it unique, like, 'aa' & matrl_desc & 'aa', while loading and in applymap. Nothing worked.

Would appreciate your insights.

Regards

Ilyas

[undergrinder]

Hi Mohamed,

The syntax of ApplyMap seems correct. I suggest you to ensure the data type of MATRL_ID is number.

And encourage you to dissect this process to steps and observe the output like,

observe the result of loading with only ApplyMap('MapMatrlDesc',MR_DTL_MATRL_DESC,0), and try in frontend the next steps e.g.: if(result>0,1,0)

G.

[undergrinder]

and observe the Materials_t and MA_MATRL_REQST_t fields to match, does it need trimming, uppercasing and so on.

[vamsee]

Adding on to Gabor's suggestions. Try the following

1. Added Len(Trim()) for checking length if it matched.
2. Used Upper() & Trim() in maps just to be sure.

-------------------------

Materials:
LOAD
MATRL_ID,
MATRL_CD,
MATRL_DESC,
MATRL_GROP_CD,
MATRL_GROP_NAME,
MATRL_GROP_CATGRY
FROM $(tQvd_Path)Materials_t.qvd (qvd);

MapMatrlDesc:
Mapping Load
Upper(Trim(MATRL_DESC)) as MATRL_DESC,
MATRL_ID
FROM $(tQvd_Path)Materials_t.qvd (qvd);

Purchase:
Load
*,
if(Len(Trim(ApplyMap('MapMatrlDesc',Upper(Trim(MR_DTL_MATRL_DESC)),NULL())))>0,1,0) AS MR_DTL_IS_MATRL
FROM $(tQvd_Path)MA_MATRL_REQST_t.qvd (qvd);

Thanks.

[iluilyas]

Thanks Gabor, Vamsee. Main issue was with the dataset. Post correcting that, it worked with ApplyMap.

Sorry for wasting your time.

Moreover, upper() and trim() helped me to iron out other data anomalies. Thanks for that