ApplyMap not working

[Simon4]

Hi all,

My ApplyMap is not working.

This is my code:

What I want: 

When Itemgroupid = 259. Look in maptable for actual Itemgroupid and overwrite old Itemgroupid.

The ApplyMap is not working and values are staying at 259. What do I need to change?

[agigliotti]

Hi @Simon4 ,

How does your mapping table looks like for CODE = 259 ?

[Simon4]

Thanks for responding.

This is a sample of my mapping table

CODE	DESCRIPTION	"PK_R_ITEMGROUP"
S0001012	Pumps	218
STCK3232	Spray	216
S0000466	E-components	231
S0000734	E-components	231
S0000815	Pumps	218
S0000805	Pumps	218
S0000817	Pumps	218
S0000847	Pumps	218
S0000795	Pumps	218
S0000824	Pumps	218

[Mark_Little]

HI @Simon4 

Would probably need to see some of data to assist more, but if it is return 259 it is not find a link. 

ApplyMap allows you to Map a single field value, so I am not sure what you are doing is the correct approach.

As you mapping on Itemgroupid which is 249, so it will only ever map back a single value. Are meant to be mapping on the Item Code? So 

IF(Itemgroupid = 249, APPLYMAP('Cateorized',Itemcode),Itemgroupid ) as Itemgroupid 

[agigliotti]

applymap function is currently searching for 259 value in column CODE, that's why it's does not works...

[Simon4]

Hi @agigliotti & @Mark_Little ,

Thank you for your time.

If I understand correctly I cannot use ApplyMap in this case?

This is my end goal:

When Itemgroupid = 259 for a certain itemcode, search in external excel file for Itemgroupid.

so:

MAIN TABLE(DATA BASE)

CODE	DESCRIPTION	"PK_R_ITEMGROUP"
STCK3996	E-components	259
S0001012	Pumps	259
STCK3232	Spray	259
S0000466	E-components	259
S0000810	Pumps	218
S0000806	Pumps	218
S0000816	Pumps	218
S0000804	Pumps	218
STCK3667	Pumps	218

EXCEL FILE

CODE	DESCRIPTION	"PK_R_ITEMGROUP"
STCK3996	E-components	231
S0001012	Pumps	218
STCK3232	Spray	216
S0000466	E-components	231
S0000734	E-components	231
S0000815	Pumps	218
S0000805	Pumps	218
S0000817	Pumps	218
S0000847	Pumps	218

 DESIRED OUTCOME

CODE	DESCRIPTION	"PK_R_ITEMGROUP"
STCK3996	E-components	231
S0001012	Pumps	218
STCK3232	Spray	216
S0000466	E-components	231
S0000810	Pumps	218
S0000806	Pumps	218
S0000816	Pumps	218
S0000804	Pumps	218
STCK3667	Pumps	218

[agigliotti · Accepted solution]

Hi @Simon4 ,

You have to modify your script as below:

IF(Itemgroupid = 249, APPLYMAP('Cateorized', Itemcode ), Itemgroupid ) as Itemgroupid

Best Regards

[Simon4]

Hi @agigliotti,

So this is my input according to your advise:

Unfortunatly this does not give me the desired outcome. With this code it formats every Itemcode with Itemgroupid = 259 as an Itemgroupid . Now I have 10 470 itemgroupid's, but in reality there are only 49.

How do I get the Itemgroupid from the excel table corresponding with these itemcodes?

kind regards,

SV

[agigliotti]

First you should check if itemcode is unique in your excel file.
Could you post a sample app with mock data?

[Simon4]

In the excel file are no itemcode duplicates if that is what you mean.

I already posted data above, is that enough? If not I'll post what you need.