Hello all, I have a table that I need to pad with missing ranges following a small, specific rule set. Lets say I have the following table (in the attached spreadsheet):I would like to result in this table, which has new rows inserted with start and end values whos ranges were not represented in the initial table. These new rows have been flagged as "free" (also in the attached spreadsheet):The knowns and the rules are as follows:The quantity of groups is not knownThe quantity of subgroups is known, in this case 1,2,3The allowed range is known for each subgroup, in this case 0-10, 10-20, 20-30As a rule, ranges should not cross subgroup boundaries (see row 9 in output table)All values from 0 to 30 should be represented in each group To simplify the example, the ranges overlap. The real data type has decimal precision, which would result in the following ranges:0 <= SUBGROUP1 < 1010 <= SUBGROUP2 <2020 <= SUBGROUP3 <=30 Thank you in advance for the help.

Solved

How to pad data in a table with missing ranges?

4 years ago
February 25, 2021
2 replies
5 views

goskoog
7 replies

Hello all, I have a table that I need to pad with missing ranges following a small, specific rule set.

Lets say I have the following table (in the attached spreadsheet):

Screenshot_1 I would like to result in this table, which has new rows inserted with start and end values whos ranges were not represented in the initial table. These new rows have been flagged as "free" (also in the attached spreadsheet):

Screenshot_2 The knowns and the rules are as follows:

The quantity of groups is not known
The quantity of subgroups is known, in this case 1,2,3
The allowed range is known for each subgroup, in this case 0-10, 10-20, 20-30
As a rule, ranges should not cross subgroup boundaries (see row 9 in output table)
All values from 0 to 30 should be represented in each group

To simplify the example, the ranges overlap. The real data type has decimal precision, which would result in the following ranges:

0 <= SUBGROUP1 < 10

10 <= SUBGROUP2 <20

20 <= SUBGROUP3 <=30

Thank you in advance for the help.

Best answer by debbiatsafe

Hi @goskoog

I'm not certain this is the most efficient way but you can approach the issue from a spatial perspective--eg. finding gaps along a line that spans the range of values (0-30). Once you have identified the gaps, you can then split those gaps at the subgroup boundaries.

I have attached an example workspace demonstrating this approach. I don't fully understand the Sub Group requirements so you will have to make some changes (see Group A feature where Start = 8 and End = 10 of output).

I hope this helps.

View original

Did this help you find an answer to your question?

+20

debbiatsafe
Safer
648 replies
Best Answer
4 years ago
March 2, 2021

Hi @goskoog

I'm not certain this is the most efficient way but you can approach the issue from a spatial perspective--eg. finding gaps along a line that spans the range of values (0-30). Once you have identified the gaps, you can then split those gaps at the subgroup boundaries.

I have attached an example workspace demonstrating this approach. I don't fully understand the Sub Group requirements so you will have to make some changes (see Group A feature where Start = 8 and End = 10 of output).

I hope this helps.

G

goskoog
Author
7 replies
4 years ago
March 2, 2021

debbiatsafe wrote:

Hi @goskoog

I'm not certain this is the most efficient way but you can approach the issue from a spatial perspective--eg. finding gaps along a line that spans the range of values (0-30). Once you have identified the gaps, you can then split those gaps at the subgroup boundaries.

I have attached an example workspace demonstrating this approach. I don't fully understand the Sub Group requirements so you will have to make some changes (see Group A feature where Start = 8 and End = 10 of output).

I hope this helps.

Hi @debbiatsafe

This solution looks really great, I will test it out today. Thank you.