Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

compare records on multiple dates

BosKev
8 - Asteroid


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.

 

DateEmployee ID
1/1/2020a
1/1/2020b
1/1/2020c
1/1/2020d
1/2/2020a
1/2/2020b
1/2/2020c
1/2/2020d
1/3/2020a
1/3/2020b
1/3/2020c
1/3/2020d
1/4/2020a
1/4/2020e
1/4/2020f
1/4/2020d
1/5/2020a
1/5/2020b
1/5/2020e
1/5/2020d
1/6/2020a
1/6/2020b
1/6/2020g
1/6/2020d
1/7/2020a
1/7/2020b
1/7/2020c
1/7/2020h
8 REPLIES 8
echuong1
Alteryx Alumni (Retired)

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.

 

echuong1_0-1600961649256.png

 

BosKev
8 - Asteroid

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
22 - Nova
22 - Nova

Hi @BosKev ,

 

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

atcodedog05_0-1600968286033.png

 

Output

Capture.PNG

 
 

Hope this meet your requirement check and let me know.

 

 

echuong1
Alteryx Alumni (Retired)

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.

 

echuong1_0-1600967468614.png

 

atcodedog05
22 - Nova
22 - Nova

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

atcodedog05_0-1600970381818.png

Workflow

atcodedog05_1-1600970414145.png

This was a fun task.

 

Hope this helps : )

 

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

BosKev
8 - Asteroid

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_1-1600973756043.png

 

BosKev
8 - Asteroid

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

atcodedog05
22 - Nova
22 - Nova

Sure no problem

 

Happy to help : )

Labels