How to verify if a timesheet date = holiday date?

[orlando162]

Hi everybody,

I'm trying to set automation to calculate overtime compensation.

I have a holidays calendar dimension [holidays] and a [timesheet date]. On my side it would be enough to create a flag whereas [timesheet date] has a value listed among the [holidays] dimension's list.

I tried:
if( match( [timesheet days], [holidays] ), 'Yes', 'No')

but it doesn't work. In similar community's cases I noticed that [holidays] should be expressed as a variable but I don't know how to create that from a dimension (honestly I don't want to type manually the whole holidays calendar).

Thank you in advance for your help.

Andrea

[Or · Accepted solution]

In script:

Load * From YourTable;

Left join

Load holidays as [timesheet days], 'Yes' as isHoliday

From YourHolidays;

You could also set this up as a mapping load if you prefer (that would also let you specify 'no' rather than null for non-holidays more easily).

Match will not work for you here because, without the context of both of those fields being a dimension within your table, you would not get a single result for each field.

[orlando162]

Thank you so much, it worked perfectly.