Setting up a count off of different date rules in ETL

JonFass
JonFass
in Magic ETL

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 Coach
    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.

    **Check out my Domo Tips & Tricks Videos

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

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    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.

    **Check out my Domo Tips & Tricks Videos

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

    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