I’m working with a dataset in KNIME that tracks ‘MOUNT’ and ‘UNMOUNT’ events with timestamps. How can I transform these events into consistent hourly data points to perform a meaningful join for my fact table?
test.xlsx (60.5 KB)
Can you provide an example of the output you’re after?
3 Likes
Thanks for your response! To clarify, I am looking to fill the gaps between ‘MOUNT’ and ‘UNMOUNT’ events with hourly data points. For example, if I have a ‘MOUNT’ event at 15:00 and an ‘UNMOUNT’ event at 20:00 on the same day, I want to create new data entries for each hour in between, so my table would have entries for 16:00, 17:00, 18:00, and 19:00.
In our dataset, “Unmount” events reflect instances where a tool or part is removed from the production line, which can happen for various reasons. For example, a tool may need to be replaced or serviced, or a part may need to be temporarily removed for inspection or adjustments during the manufacturing process. These events are important for tracking the workflow and understanding tool usage patterns, maintenance schedules, and potential downtimes in the production cycle.
Hi @sasoriza.
Welcomme to the KNIME user forum.
Have a nice new year.
I am curious about how you handle consecutive MOUNT MOUNT or UNMOUNT UNMOUNT. Or MOUNT without UNMOUNT.
My first thought would go to nodes:
COLUMN EXPRESSIONS to Join DATE +“T”+ TIME and make a date time field.
SORTER to sort rows by date&time in ascending order
LAG COLUMN for previous date time
LAG COLUMN for previous TRANSACTION in the same line
Rule base row filter to separate sequence MOUNT MOUNT and UNMOUNT UNMOUNT from MOUNT UNMOUNT
Group loop with 1 for each row
Create date time interval 1h generate lines for hours
End loop to collect hours rows
Sorter on date time.
Hope I helped you.
Best regards
Just some thoughts,
Calculate the difference between “start” and “end” time as new column then use one row to many node. Use a helper node like rank to get the hours to add for each new row and finally add this column to the “start” date
br
2 Likes
I had a hard disk crash which has kept me out of commission. I’m back up and will take another look.
This isn’t the output format you wanted, but it may be useful. It has the total hours for each event. It has a blank for the final event for each tool since there’s no endpoint to calculate a time. I’m sure your desired output format is doable but the workflow will be pretty complicated probably requiring a nested loop although I haven’t given it much thought.
Hi @sasoriza ,
This workflow uses “difference calculation” the One Row to Many node as mentioned by @Daniel_Weikert
becomes
2 Likes
Here’s a slightly different version from @takbb’s. Just goes to show there’s usually no single Knime solution.
2 Likes
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.