Rename of Field Value where it more than once

[Anonymous]

Hi

I have the following table below where Employees has taken leave in a month. So where an Employee has taken more than one type of Leave (those Employees highlighted in blue), I want the value for the  Type of Leave to change to 'Combination'.

How do accomplish this.  I want to do this in the back end.

Kind regards

Nayan

Employee	Type of Leave	Leave Days Taken
2216	Sick Leave	5
2216	Annual Leave Commutation	20
162345	Annual Leave	1
1761064	Annual Leave	1
1761064	Annual Leave Commutation	10
1796690	Annual Leave	1
1960393	Family Responsibility Leave	1
1986821	Annual Leave	1
2021048	Annual Leave	3
2060892	Sick Leave	1
2081350	Sick Leave	4
2081857	Sick Leave	2
2125943	Annual Leave	1
2125943	Annual Leave Commutation	7
2126036	Family Responsibility Leave	1
2150506	Annual Leave Commutation	7
2150875	Annual Leave	1
2150875	Sick Leave	3

[sunny_talwar · Accepted solution]

Check this

Table:

LOAD * INLINE [

    Employee, Type of Leave, Leave Days Taken

    2216, Sick Leave, 5

    2216, Annual Leave Commutation, 20

    162345, Annual Leave, 1

    1761064, Annual Leave, 1

    1761064, Annual Leave Commutation, 10

    1796690, Annual Leave, 1

    1960393, Family Responsibility Leave, 1

    1986821, Annual Leave, 1

    2021048, Annual Leave, 3

    2060892, Sick Leave, 1

    2081350, Sick Leave, 4

    2081857, Sick Leave, 2

    2125943, Annual Leave, 1

    2125943, Annual Leave Commutation, 7

    2126036, Family Responsibility Leave, 1

    2150506, Annual Leave Commutation, 7

    2150875, Annual Leave, 1

    2150875, Sick Leave, 3

];

Left Join (Table)

LOAD Employee,

Count(DISTINCT [Type of Leave]) as Count

Resident Table

Group By Employee;

FinalTable:

LOAD *,

If(Count = 2, 'Combination', [Type of Leave]) as [New Type of Leave]

Resident Table;

DROP Table Table;

[Anonymous]

Thank you Sunny.  It worked.

Kind regards

Nayan