Dates

Hi,

I need help with dates plz.

So, If I had a huge excel file with employee who were hired and fired. I have to determine when was there first day with the company and when was there last day, but the trick is that some of them were hired more than once within the same organization. For example, got hired Jan 1st, got fired Jan 31st. Then got rehired in November. This would be reflected in 2 separate rows in the data

If I imported data as usual, only the first row will show up (from Jan 1st to Jan 31st), there will be no mention about the other hiring in November. how can I show their first day and last day (Jan 1st & November) while showing that they took a break from Jan 31st to November (this can be in a different field)?

Workflow

Tips and Tricks

Date Time

Transformation

Preparation

Accepted answers

All comments

DanM

@AHM

I would highly recommend providing sample data here as well as sample workflow as to what you've tried thus far in Alteryx. I would also send a sample of what you want in the end. Without the data it would be difficult to give you a reply since we don't know the structure of your data.

DanM

Tyro_abc

Hi,

Can you provide some kind of sample data set. I will be glad to help you.

Sample data set would help me to understand the problem.

Best Regards

Arundhuti

danilang

Hi @AHM

To get the ultimate first and last dates, use a Summarize tool grouped by employee and select the min and max dates.

To get the ranges Sort by employee and start date. Then use a Multi Row tool grouped by id to generate a GapStartDate equal to [row-1:EndDate]+1 day and then a formula tool to subtract 1 day from the current StartDate to give the GapEndDate. The syntax to add datetime values is DateTimeAdd([Field],N,units). N would be 1 and -1 to add and subtract and units ="days"

Dan

AHM

Thank you @danm,

So here is a sample. Employee X was terminated, but then rehired.

I want it as follow

Before, I used to do this manually on excel. I used to do a pivot table and put "count" to reflect how many lines they have. and then do a VLOOKUP from the pivot table to get the count and if it was more that 1, I would check it manually.

AHM

Thank you @Tryo_ABC,

So here is a sample. Employee X was terminated, but then rehired.

I want it as follow

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703