Merging two time series with different times and different dimensions

[bbuclin]

Hi. I’ve got two time series, each having one row per timestamp and different columns for each time series.
The first one tells me the assigned number of widgets for a resource at a given time:
timestamp ResourceName Capacity
1 Jan A 10
7 Jan B 5
10 Jan A 15

assignments are infrequent (like once every few days). I’ve got about 3000 resources.

The second one tells much how many widgets are used at the timestamps by how many folks:
timestamp ResourceName WidgetsInUse #users
1 jan 1am A 4 5
15 jan 2am A 3 2
8 jan 1am B 2 10

and so on… utilisation records are very frequent (every 5 minutes actually, close to a million a day).

I’d like to merge those two series to tell on a single row how many widgets are available at a point in time, how many are in use and by how many users. Something like:
timestamp ResourceName WidgetsInUse #users Capacity
1 jan 1am A 4 5 10
15 jan 2am A 3 2 15
8 jan 1am B 2 10 5

essentially adding to each of the million utilization records what is the available capacity for that resource at that point in time.

Any suggestion on how to achieve this?

[morpheus]

Hi @bbuclin,
looking on your example, the only key you can use is the date without any time information.
Due to the date format you only can deal them as on string or split them into day and month. In both cases you should transform them to upper or lower case.

Now my suggestion for a solution

1. Split date information of both tables using the column splitter node
2. concatenate both results (date) of step 1
3. use groupby node for the concatenated table (group criteria: date, no aggregations requiered) to get unique dates.
4. Join both inital tables using the column joiner node to the result of step 3 using left outer join.

BR
Hermann

[bbuclin]

I’m sorry I oversimplified my case description: the timestamps in both time series are properly formatted Date-Time dimensions (like 2019-06-29T10:35:12)

[morpheus]

Then forgot the upper case section and start directly with point 1. The joining key is your date-time column.

[bbuclin]

Thanks for the suggestion… but it does not achieve what I’m looking for. I’ve crafted a small workflow that implements your suggestion, and added at the end of it a table that shows my intended outcome, i.e. what I"m looking to acheive. Any hint on how to get there is much welcome
Join time series.zip (55.4 KB)

[pigreco]

Try this one:
Join time series_pigreco.knwf (38.1 KB)

[bbuclin]

THANK YOU! That did the job. Perfect.