Advanced cross tab calculation

[DM_88]

Hi all

I want to do a cross tab calculation but I don't know were to start.

The thing that I want to do is transform next data:

ID	Start	Stop
A	6/2/2021 0:00:02	6/2/2021 0:07:32
B	6/2/2021 0:00:04	6/2/2021 0:01:17
C	6/2/2021 0:00:05	6/2/2021 0:00:14
D	6/2/2021 0:00:05	6/2/2021 0:01:21
E	6/2/2021 0:00:06	6/2/2021 0:01:48
F	6/2/2021 0:00:07	6/2/2021 0:03:19
G	6/2/2021 0:00:08	6/2/2021 0:00:10
H	6/2/2021 0:00:09	6/2/2021 0:03:09
I	6/2/2021 0:00:10	6/2/2021 0:00:15
J	6/2/2021 0:00:10	6/2/2021 0:03:07
K	6/2/2021 0:00:11	6/2/2021 0:00:16
L	6/2/2021 0:00:14	6/2/2021 0:00:16

Into this:

			Timeline
ID	Start	Stop	6/2/2021 0:00:00	6/2/2021 0:00:01	6/2/2021 0:00:02	6/2/2021 0:00:03	6/2/2021 0:00:04	6/2/2021 0:00:05	6/2/2021 0:00:05	6/2/2021 0:00:06	6/2/2021 0:00:07	6/2/2021 0:00:08	6/2/2021 0:00:09	6/2/2021 0:00:10	6/2/2021 0:00:10	6/2/2021 0:00:11	6/2/2021 0:00:12	6/2/2021 0:00:13	6/2/2021 0:00:14	6/2/2021 0:00:15	6/2/2021 0:00:16	6/2/2021 0:00:17
A	6/2/2021 0:00:02	6/2/2021 0:07:32			1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
B	6/2/2021 0:00:04	6/2/2021 0:01:17					1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
C	6/2/2021 0:00:05	6/2/2021 0:00:14						1	1	1	1	1	1	1	1	1	1	1	1
D	6/2/2021 0:00:05	6/2/2021 0:01:21						1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
E	6/2/2021 0:00:06	6/2/2021 0:01:48								1	1	1	1	1	1	1	1	1	1	1	1	1
F	6/2/2021 0:00:07	6/2/2021 0:03:19									1	1	1	1	1	1	1	1	1	1	1	1
G	6/2/2021 0:00:08	6/2/2021 0:00:10										1	1	1	1
H	6/2/2021 0:00:09	6/2/2021 0:03:09											1	1	1	1	1	1	1	1	1	1
I	6/2/2021 0:00:10	6/2/2021 0:00:15												1	1	1	1	1	1	1
J	6/2/2021 0:00:10	6/2/2021 0:03:07												1	1	1	1	1	1	1	1	1
K	6/2/2021 0:00:11	6/2/2021 0:00:16														1	1	1	1	1	1
L	6/2/2021 0:00:14	6/2/2021 0:00:16																	1	1	1
			0	0	1	1	2	4	4	5	6	7	8	10	10	10	10	10	11	10	9	7

So at the end I want to see how many ID's per second were active.

Do's anyone of you have the solution?

 Thanks a lot!

[apathetichell]

There are 451 seconds which your rows cover. do you want 451 columns? If I may make a suggestion - take the first and last times. Create rows for each second. Use a batch macro to filter. Do a count of how many active entries there are by second. Oh and do some sweet date/time conversions along the way...

Oddly something identical to this suggestion has been attached to this comment.

[danilang · Accepted solution]

hi @DM_88 

Here's a non macro solution.

The top branch generates all the seconds that each item is active and then groups by second to find the count for each second.  The bottom one generates all possible seconds between the start and the end to fill in any missing gaps.

This workflow outputs the list of seconds as rows, because it will be much easier to deal with.   If you require the output in columns, add a crosstab at the end

Dan 

[apathetichell]

@danilangI think the key is "don't have 451 columns"

[DM_88]

@apathetichell 

Thanks to reply to my topic!

U can ignore the DateTime setup, it's the format that our stakeholder is working with 😊 unfortunately I can not open your zip file because you used an newer version as me. For company related reasons we are using 2021.1, but I'm still curious to your solution.

Thanks!

[DM_88]

@danilang 

Thanks to reply to my topic!

Your output is indeed what I'm looking for, so that's the reason why I'll flag your post as the solution! 👍

Many thanks for helping me! I thought it should be a complex calculation but when I see your flow it looks quit ok 😊

Thanks!

[apathetichell]

Hi - mine is similar to @danilang 's as it takes max and min times and generates all seconds in between. Mine then uses that as a data source and runs the original into a macro to do the filtering there. It's cleaner - but his way is much more straight forward. 

Just today @atcodedog05 posted detailed instructions on how to open workflows created with newer versions of designer in a blog post here: https://community.alteryx.com/t5/Engine-Works/Making-Workflows-Apps-amp-Macros-Backwards-Compatible/...