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

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.