Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Grouping overlapping contracts with first/last dates

I have an export of contract data where overlapping contracts need to be grouped into continuous dates, with the first/last dates for each group. Individual IDs may have more than one group of contra...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Here a complete solution (to test, of course, with more significant data).

     

     

    let

    #"Raggruppate righe" = Table.Group(yourTable, {"ID"}, {{"Conteggio", each Table.RowCount(_), type number}, {"startend", each CSEsorted( _[Contract_Start], _[Contract_End])}}),
    #"Tabella startend espansa" = Table.ExpandListColumn(#"Raggruppate righe", "startend"),
    #"Valori estratti" = Table.TransformColumns(#"Tabella startend espansa", {"startend", each Text.Combine(List.Transform(_, Text.From), ":"), type text})
in
    #"Valori estratti"

     

     

    this modified function does not rely on the order of identifiers.

     

     

    let 
CS_CE=(start as list, end as list)=>
let

startend=List.Sort(List.Zip({start,end}),(x,y)=>Value.Compare(x{0},y{0})),
unionInt=List.Accumulate(startend, [CS={List.Min(start)},CE={List.Min(start)}], 
                                             (s,c)=> [CS= if c{0}>List.Last(s[CE]) then s[CS]&{c{0}} else s[CS],
                                                      CE= if c{0}>List.Last(s[CE]) then s[CE]&{c{1}} else 
                                                  if c{1}>List.Last(s[CE]) then List.RemoveLastN(s[CE],1)&{c{1}} else s[CE]])
in

List.Zip(Record.ToList(unionInt))

in CS_CE

     

     

     

    in this case the result seems what you expect, but I suggest you test other situations as well

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

A solution that does not use the table.group:

 

 

 

    let
    cp = Table.AddColumn(yourtab, "grpIdx", each Table.PositionOf(tab1[[ID],[Contract_Start],[Contract_End]],[[ID],[Contract_Start],[Contract_End]],Occurrence.All,(x,y)=> x[ID]=y[ID] and 
                                                                   IntervalIntersect({x[Contract_Start],x[Contract_End]},{y[Contract_Start],y[Contract_End]})  )),
    ap = Table.AddColumn(cp, "union", each let un=List.Accumulate(cp[grpIdx],{},(s,c)=> if not List.IsEmpty(List.Intersect({_[grpIdx],c})) then List.Union({s,c}) else s) 
                    in Text.Combine(List.Transform({cp[Contract_Start]{List.Min(un)},cp[Contract_End]{List.Max(un)}}, Text.From), ":")),
    #"Rimossi duplicati" = Table.Distinct(ap)
in
    #"Rimossi duplicati"

where IntervalIntersect is the following function:

= (x ,y)=>
let ovl= x{1}>y{0} and x{0}<y{1} in ovl

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

in an attempt to solve this problem, I thought it might be useful (even just as an exercise) to have a function that, from a generic list of intervals, obtains the union-of-these-intervals-list.

 

Here the code:

 

 

let 
union=(lstInt as list)=>
let
sortU=List.Sort(lstInt,(x,y)=>Value.Compare(x{0},y{0})),
UI=List.Accumulate(sortU, {{null,null}}, 
                        (s,c)=> if IntIntersection(c,List.Last(s)) 
                                then List.Union({List.RemoveLastN(s,1),{{List.Min({c{0},List.Last(s){0}}),List.Max({c{1},List.Last(s){1}})}}})
                                else List.Union({s , {c}})
                   )
in UI
in union

 

 

 

the function IntIntersection, to check if two intervals intersect

 

 

let
overlapInt=(x ,y)=>
let  ovl= x{1}>=y{0} and x{0}<=y{1} , 
OVL=if ovl=null then true else ovl
in OVL
in overlapInt

 

 

 

here how you can use it in the problem:

 

 

 

 

...

    grp = Table.Group(youTab, {"ID"}, {"StartEnd", each IntUnion(List.Zip( {_[Contract_Start], _[Contract_End]}))}),
    te = Table.ExpandListColumn(grp, "StartEnd"),
    ve = Table.TransformColumns(te, {"StartEnd", each Text.Combine(List.Transform(_, Text.From), ":"), type text})
in
    ve