I have data stored in a database table, where a value is recorded month by month:
ValidFrom
ValidTo
ID
Value
01-01-26
31-01-26
A
0,98
01-02-26
28-02-26
A
0,98
01-03-26
31-03-26
A
0,98
01-04-26
30-04-26
A
1,02
01-05-26
31-05-26
A
1,02
01-06-26
30-06-26
A
1,02
01-07-26
31-07-26
A
0,96
01-09-26
30-09-26
A
1,02
01-10-26
31-10-26
A
1,02
I'd like to group consecutive rows that have the same value into a single row, by combining their ValidFrom and ValidTo dates.
However, if I use an enumerate-like approach, it simply groups all identical values together, ignoring whether the dates are consecutive or not.
The desired output would be:
ValidFrom
ValidTo
ID
Value
01-01-26
31-03-26
A
0,98
01-04-26
30-06-26
A
1,02
01-07-26
31-07-26
A
0,96
01-09-26
31-10-26
A
1,02
This is a simplified example to explain what I'm trying to achieve.
In reality, the ID is part of a much more complex structure with many other columns.
I believe I could use the shift column action to help with this, but it gets quite tricky when I need to shift and compare dozens of columns and build all the required conditions.
By the way, I think it would be helpful to have an option in the enumerate action to reset the group number when rows are not consecutive.
Thank you for your help and ideas. My issue mainly came from the fact that the 'ID' column was actually a set of conditions based on 14 columns, and I didn't want to create 14 "shift columns" and 14 "if col1 = col2 and col2 = col3, etc." I worked around the problem by concatenating these 14 columns into a single one and based all the shift logic on that column, along with a shift column on the end date to account for non-continuous months with the same set of conditions.
However, I think adding an option to increment the counter in the "enumerates" actions for discontinued groups could simplify this kind of manipulation. @dgudkov, do you think this suggestion could be considered for a future release?
Shifting columns as suggested by @AndreaM and @Jochen_Marquardt seems to be the right approach. Can't think of a better way.
The case appears to me quite specific and its solution using existing actions is not too complex.
Another option is to use the "Enumerate groups" action and select the 14 columns as those which combination defines the groups. The only advantage of this option is that you will get nice short numbers as identifiers instead of long text strings.
