Collapse Dates into ranges

[samkp]

Hi,

I want to know if I can collapse dates?

Input

MEMBER	COVERAGE PERIOD
M1	5/1/2015
M1	6/1/2015
M1	7/1/2015
M1	8/1/2015
M1	9/1/2015
M1	10/1/2015
M1	11/1/2015
M1	3/1/2016

Output

MEMBER	START SERVICE	STOP SERVICE
M1	5/1/2015	12/1/2015
M1	3/1/2016	4/1/2016

I don't want to use the Min and Max as it will not give the break downs in the periods. Can the above output be achieved?

[Storm · Accepted solution]

As with everything in Alteryx, multiple approaches are possible. Here's mine.

• First, sort by member number and coverage period (both ascending).
• Next, insert multi-record tool, title field STOP SERVICE. Format as date. Formula is:
  IF [MEMBER] = [Row-1:MEMBER] and DateTimeDiff([COVERAGE PERIOD],[Row-1:COVERAGE PERIOD],"months") = 1
  then DateTimeadd([COVERAGE PERIOD],1,"months")
  else null() endif
• Next, a second multi-record tool, title field START SERVICE. Format as date. Formula is:
  IF [MEMBER] = [Row-1:MEMBER]
  and DateTimeDiff([COVERAGE PERIOD],[Row-1:COVERAGE PERIOD],"months") = 1
  and !isnull([STOP SERVICE]) then [Row-1:START SERVICE]
  else [COVERAGE PERIOD] endif
• Next, a summarize tool. Group by MEMBER, then START SERVICE, and get the MAX of STOP SERVICE. Title this field STOP SERVICE within the summarize tool.
• Finally, add a formula tool, have it look at STOP SERVICE, and apply this formula:

if isnull([STOP SERVICE]) then DateTimeadd([START SERVICE],1,"months") else [STOP SERVICE] ENDIF

Test it out, examine the outputs at various steps, make sure it's doing what you want. I tested it with success on your dataset, then I expanded your dataset, adding other members, and other months, all out of sequence. Output seemed correct to me.

[Storm]

Example workflow attached.

[samkp]

Thanks @Storm. Your solution is excellent. 

I am kinda having trouble with the "STOP" function..See attached excel sheet. I am guessing it has to do with way to sort but I tried to my level best..and still couldn't get the o/p. Any help on this is greatly appreciated!!

 Not all records are of same scenario. The logic works where the where the row-1 record date is greater than the row date (example "transfer to" rows from the i/p file) but there are some records where the row-1 record date is less than the row date (transfer from rows from the i/p file).. Can we include both the scenarios in the logic?

[samkp]

See attached workflow

[samkp]

I kinda played around and found the solution