Assign a value based on two dimension's values

[andymanu]

Hi All,

I want to assign a code value for each data row based on the values of two dimensions in a data set.

Note

The below data set is a hypothetically generated data set to explain my question.

Please refer the below sample data set,

Department	Area	Outcome Code
Finance	A	1
HR	C	2
Marketing	A	3
Sales	A	4
HR	B	5
Marketing	C	6
Finance	B	7
IT	A	8
Finance	D	9
HR	D	10
Marketing	B	11
Sales	B	12
Marketing	D	13
IT	B	14

The "Outcome Code" is the output value I want to generate based on the unique combined values of the "Department" and "Area".

For example, if the department is "Finance" and the Area is "A", then the Outcome Code should be 1.

I know I may use an "If" statement to accomplish the task with the AND operation. However, my actual data set got over 220 unique combined data records. So interested in looking for a different method.

Also, unfortunately "ApplyMap" function cannot be used since I got two dimension values to be evaluated to generate the Outcome Code value.

Appreciate, If someone can propose a suitable solution to accomplish the task.

Thanks in advance.

Kind regards,

Andy

[tresesco · Accepted solution]

You could perhaps create a composite key and use applymap instead.

[timpoismans]

As Tresesco suggests, I think the best solution in this case would be to create a composite key out of Department and Area and use applymap with this key.

Regards,

Tim P.

[andymanu]

Hi Tresesco & Tim,

Thanks for your prompt reply, I'll try the suggestion and get back to you soon.

Thank you all.

Kind regards,

Andy

[andymanu]

Hi Tresesco & Tim,

Thank you very much for the reply.

Yes, it seems to be one of the best way to resolve it and it's working.

Kind regards,

Andy