Setting up a count off of different date rules in ETL

JonFass
JonFass ⚪️
in Dataflows

Hi everyone,

I'm trying to set up a column that would show me how many workdays to pay a consultant for, depending on how many days they worked for over the month.

The variables are whether the start or end date was during that month (and so they would only be comped for that part of the month - from start to month end of form month start to work end).

I've tried to set up an ETL process to give me the month and year for different date points to compare against one another to see which rule should apply. I then tried to set up a case to give me the relevant number of workdays from different columns in the data set, depending on the situation (below), but keep getting a syntax error message.

Any ideas on how I could make this work?


image.png


Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷
    Answer ✓

    From a syntax perspective, you are missing an END statement. You only have one, but you need two because you have two CASE statements.

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • mhouston
    mhouston 🟢

    What's your dataset structure like? If you can get it to a row per day worked, you could join to the calendar dimension, and then do a group by on month and year to count the number of days worked.

  • JonFass
    JonFass ⚪️

    This is exactly what I needed, thank you!

Categories