Dates

[AHM]

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)? 

[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 

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. 

[afv2688]

Hello @AHM ,

I came up with a solution, look at it and let me know.

Regards

[Tyro_abc]

Hi @AHM 

I created some extra rows for testing purpose. Let me know if this workflow works.

 Input Data:

2.Terminate 'm'  on 5/11/2020

3. Then add new hire date for 'm' 6/13/2020

Best Regards

Arundhuti