Status tracking for hot board

gbrown · 2022-08-25T19:10:10+00:00

There was an error rendering this rich post.

I am wanting to track a hot board status for part numbers and want to use the status to track through the process. Is there a formula or beastmode that can remove the in process data once the ticket has been fulfilled and reads 'completed'. For example…

Once the part number 3333-000 by Robert Storey was completed can the ‘In process’ row be removed and only track the completed status until a new ticket for that part number has started.

thanks

Find more posts tagged with

SQL

Cards

Magic ETL

Accepted answers

RobSomers

@gbrown You'll want to use an ETL with the Rank & Window tile to create a 'Current Status' column. You'll want to do use RowNumber and order on Timestamp in descending order, and then partition by part number (and owner if a part number can have multiple owners at the same time). This will give the row with the latest time stamp a row number of 1. You'll then filter the rows to only include row number 1 and you can rename your status column to 'Current Status' in order to differentiate it for the original status column. Then join this back to your original data set, and you can filter out in the ETL or on your card, any row that has 'Current Status' = 'Completed'. Then if it ever pops up in the data again and is in process, it will appear again because that's the latest status.

All comments

RobSomers

gbrown

This worked perfectly. Thank you for your help on this!

jaeW_at_Onyx

@gbrown i like @RobSomers approach, but might extend it.

it sounds like you work on each item multiple times in a day, year, month.

so i might create a counter that tracks "this is the n'th time" somoene worked on this part. ideally i'd have an order_id, but in the absence of that i'd just ask if there's a new person working on this part. (is_same_owner = case when owner <> lag_1_owner then 1 else 0

then if i take the cumulative sum of is_same_owner i know "n_th_iteration_of_product'

then "is_completed = case when current_status=completed then 1 else 0 end"

now to know if the is_ticket_completed i can take max(is_completed) over (partition by n_th_iteration_of_product)

I can exclude all rows where is_ticket_completed = 1

this approach might give me insight into "how many times did we complete this item this month" "what is the average time to complete an item." and other stats. Just by running it through a ton of window() functions -- do it in Magic of course! :P