Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
iluilyas
Contributor III
Contributor III

ApplyMap() Vs Lookup() - Material Description

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

4 Replies
undergrinder
Specialist II
Specialist II

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
Specialist II
Specialist II

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

vamsee
Specialist
Specialist

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
Contributor III
Contributor III
Author

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