if value included in a list

[orlando162]

Dear all,

I would like to find out if a specific value is included in a list. I tried with match function but something wrong.

if(match(DAY,HOLIDAYS)
and match(COMPANY,HOLIDAYS_COMPANY),
sum(timesheet)*1.5,
sum(timesheet) )

Someone can help me? I'm looking to find out if an employee has worked in an HOLIDAY (listed in a specific calendar of his own HOLIDAYS_COMPANY), to calculate timesheet to be compensated as overtime.

Please find attached a screenshot of my holidays calendar to be matched with timesheet data of employees.

Thank you,
Andrea

[stevejoyce · Accepted solution]

Assuming you have a daily timesheet table with date, employee, hours ...

And your Holiday table.

You can use applymap to create a flag/weighted salary metric to use in your calculation

holiday_weight:

mapping load distinct

Holiday

,1.5

resident [holiday table]

;

timesheet_data:

load *

,applymap('holiday_weight', [timesheet date], 1) as holiday_weight

from/resident [timesheet data table];

now your calculation in the front end can be 

sum([hours] * [holiday_weight])

[orlando162]

Thank you for your reply.

Could work, but how to make double check for holiday calendar and company? Every company has its own holiday calendar.. I thought match was easier but maybe applymap is better

[stevejoyce]

Match returns where a searched value is in a list, that isn't going to help if you are looking in another table.

So you have 1 holiday table for all companies, and i assume a Company field in your holiday table?

You can create a composite key of date & company that is used in your apply map.