compare records on multiple dates

[BosKev]

Hi All,

I have a table with snapshot dates, each date there are multiple employee records with unique employee ID.

I want to be able to compare each date and find out who left or who got added. what's the most efficient way of doing this without creating hundreds of join tool? Thanks.

Date	Employee ID
1/1/2020	a
1/1/2020	b
1/1/2020	c
1/1/2020	d
1/2/2020	a
1/2/2020	b
1/2/2020	c
1/2/2020	d
1/3/2020	a
1/3/2020	b
1/3/2020	c
1/3/2020	d
1/4/2020	a
1/4/2020	e
1/4/2020	f
1/4/2020	d
1/5/2020	a
1/5/2020	b
1/5/2020	e
1/5/2020	d
1/6/2020	a
1/6/2020	b
1/6/2020	g
1/6/2020	d
1/7/2020	a
1/7/2020	b
1/7/2020	c
1/7/2020	h

[echuong1]

You can use a cross-tab to pivot your data and see your values on a per employee and per date basis.

You can easily see if an employee was listed or if there are gaps.

[BosKev]

Hi @echuong1 ,

Thanks for the help , I probably didn't explain this well. I'm looking to identify the adds/losses daily by comparing daily employee records. I attached a workflow comparing just 1 day to see where the gains/losses coming from. is it possible to setup some type of loop to repeat this workflow until it runs out of date to compare? There are at least a year worth of dates in the table I'm hoping for something efficient in Alteryx. Thanks.

-BosKev

[atcodedog05]

Hi @BosKev ,

After working for a while i was able to come up with this workflow to meet the task.

Output

Hope this meet your requirement check and let me know.

[echuong1 · Accepted solution]

I'm not sure if the values from your workflow make sense? They don't seem to line up with the data.

Take a look at this workflow. It may be what you're looking for. You may have to adjust the logic a bit.

[atcodedog05]

Hi @BosKev ,

I heard what you took it and did exactly the same.

Took your workflow and changed it into batch macro to iterate.

Macro

Workflow

This was a fun task.

Hope this helps : )

If this helps your task then please mark it as a solution

[BosKev]

Thanks @atcodedog05

Appreciate the help and creativity on this one; I went with @echuong1's  wf its got less steps.

Also, the last filter on the wf had a hard code date which might not work if the date range in the input table is dynamic.

[BosKev]

Thanks...I change the data in the input to show some activities for the first couple of days. Sorry it throw you off.

[atcodedog05]

Sure no problem

Happy to help : )