Creating a New Field

[Anonymous]

Hi

I have a list of Employees who has taken leave.  (see file attached).  How do I create a field (in the backend preferably)  that indicates "Yes' for Employees who has taken at least once "Taken more than 10 days" leave and 'No' for the alternative

So, in the screenshot below, for Employee 11, he has "Taken more than 10 days" , ie 18 days.  So this employee therefore should have 'Yes' to his name.  Whereas Employee 100, 'No' is assigned to his name.

Kind regards

Nayan

[Anil_Babu_Samineni]

May be this?

If([Leave Days] > 10, 'Yes', 'No') as [Taken More Than 10 Days]

[Anonymous]

Hi Anil

Thank you for your reply.  However, as mentioned, for eg, Employee 11, I want a field, so that  'Yes' to appear for every transaction line for this Employee.

The formula that you have give, already have that.

Kind regards

Nayan

[Anil_Babu_Samineni]

that means, Employees 10, 101, 102, 11 should be Yes and 1, 100, 12 should be no, Is this your question?

[Anonymous]

Yes, that is correct.

[devarasu07 · Accepted solution]

Hi,

Try to use mapping load and apply map fxn

mapEMP:
mapping load 
[Employee Number],
 if([Leave Days]>10,'Yes','No') as [Taken More than 10 Days]
 FROM

(ooxml, embedded labels, table is Sheet1);

Fact:
load *, ApplyMap('mapEMP', [Employee Number],'NA') as  [Taken More than 10 Days];
LOAD [Employee Number], 
    [Employee Name], 
    LeaveStartDate, 
    LeaveEndDate, 
    [Employees within 3 Months Cycle], 
    [Leave Days], 
    [Days Taken]    
FROM

(ooxml, embedded labels, table is Sheet1);

[gys1992infosys]

Hi Nayan,

For example: If ([Leave Days]>10,'Yes','No'), this expression can also apply in colour:

if([Leave Days]>10,red(),yellow())

[Anil_Babu_Samineni]

May be this?

If([Employee Name] = Previous(Employee Name] and [Leave Days] > 10, 'Yes', 'No') as [Taken More Than 10 Days]

[avinashelite]

As per my understanding you need Yes or No based on the Complete year .....

if the is the case try like this

LOAD

if(Total_Leave_Days>18,'Yes','No') as Total_Leave_Days,

[Employee Number];

LOAD

SUM([Leave Days]) as Total_Leave_Days,

[Employee Number]

resident

main_table

group by

[Employee Number];

NOTE: if you need year wise you could inculde year also

[mathieupignon]

Hi,

You can manage this in the Data Load Editor, with this Load Script:

[Sheet1]:
LOAD
    "Employee Number",
    "Employee Name",
    LeaveStartDate,
    LeaveEndDate,
    "Employees within 3 Months Cycle",
    "Leave Days",
    "Days Taken" 
FROM [lib://Desktop/Leave Test.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Results]:
LOAD
    "Employee Name",
    MAX("Leave Days") as "Max Leave Days",
    IF(MAX("Leave Days")>10,'Yes','No') as "Taken More than 10 Days"
RESIDENT [Sheet1]
GROUP BY "Employee Name";

That results in