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

Try using this function for each group:

 

 

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

aaa=List.Accumulate(List.Zip({start, end}), [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

aaa

in CS_CE

 

At the moment I don't have enough time to write a complete solution.
If it is needed and the function is considered valid (tests can be done by providing ad hoc lists), I will do it later.

Anonymous's avatar
Anonymous
Not applicable
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