Best Of

You can use a formula tile to splice out the different parts and assemble it as a timestamp:

TIMESTAMP(CONCAT (
  LEFT(`Date Hour and Minute`, 4), '-',
  SUBSTRING(`Date Hour and Minute`, 5, 2), '-',
  SUBSTRING(`Date Hour and Minute`, 7, 2), ' ',
  SUBSTRING(`Date Hour and Minute`, 9, 2), ':',
  SUBSTRING(`Date Hour and Minute`, 1, 2)))

@MarkSnodgrass thank you!! This was really helpful (and much simpler than what I was thinking) - I was able to take this and run with this to get what I need.

Writing up the full solution for anyone who runs into something similar:

I anonymized a subset of the data and ended up finding some edge cases I hadn't considered - 1) resources with no assignment and 2) unassigned days before the first assignment for the resource and after the last assignment for a resource and 3) overlapping resource assignments i.e. Assignment A and B have the same start date but different end dates:

I applied the logic you gave me (and added a sort on end date in the rank and window to handle overlapping assignments like curly's that both start 10/13) which filled in all the gaps:

I was then able to extend the logic to handle these red bars:

The case where nobody is assigned is a simple formula for start date is null and end date is null.

For the days before the first start date:

1. Rank and window tile - create a column last_start_date that is the lag of start date, ordered on start and end date
2. Formula tile:

For the days after the last end date:

1. Rank and window tile - create a column next_end_date that is the lead of start date, ordered on start and end date
2. Formula tile:

Then append all of these to the original dataset and boom, magic.

Now I just have to switch in the real dataset to this logic :)

I would suggest keeping your data in your original format and then building out your unassigned dates by using the rank and window tile and a formula tile. I did it by doing the following:

1. Connect your original dataset to a rank and window tile and then create a column called Last End Date and choose the Lag function using the End Date column with an offset of 1. Order by the start ascending and partition on the resource name
2. Add a formula tile next and create 3 columns that look like this

This will result in an output like this:

You can then filter to unassigned equal 1 and overwrite your assignment name column to unassigned and then append to your original dataset using the unassigned start and end dates as your start and end dates.

Hope that makes sense.

Using your screen shots I built a webform and tested this out and I got the same results. I added in an "Alter Columns" tile and CAST the "Total Revenue" column to be a Fixed Decimal and after that the Rank & Window returned 0.0 on the rows I expected to be 0.

I can't remember exactly why this happens but it is something to do with binary and scientific notation not playing well together.

@TheLookout Tried it out and it worked perfectly! Thanks so much for your help :)

During the Technical User Group kickoff call, @john_stevens dropped some knowledge on us that just blew my mind. As seen in the gif below, you can copy Magic ETL tiles to the clipboard, and then paste into a text editor and it will give you the JSON definition of those tiles. That definition file can then be edited and repasted into the Magic canvas to produce new tiles. This can be incredibly helpful if you're wanting to edit Magic in bulk and don't want to use the Java CLI. I'm really excited to try this out more!

-Elliott 😁