What are the challenges you face when working across database platforms? Take the survey
Options

Generating a set of related rows

AlexMBanksAlexMBanks Posts: 15 Bronze 2
edited January 11, 2017 9:56AM in SQL Data Generator
Some of our business data is logically grouped into sets and I can't find any way to generate data in this format with SDG. The sets are composed of a series of values with numeric indices and we receive a number of these sets, which must be complete. For example, if the set size is 3 our table might look like this:

We have 3 sets (A, B, C) and they each have exactly 3 rows, indexed 1-3. The values are random integers.
|RowId|Set|Index|Value|
|1    |A  |1    |50   |
|2    |A  |2    |100  |
|3    |A  |3    |75   |
|4    |B  |1    |30   |
|5    |B  |2    |40   |
|6    |B  |3    |50   |
|7    |C  |1    |11   |
|8    |C  |2    |10   |
|9    |C  |3    |9    |

The real data sets have several hundred items in each set and multiple columns to uniquely identify each set.

Is there any way to generate these sorts of sets in SDG? It seems like the generators work purely on a row-by-row basis but I'm hoping to be proven wrong.

Comments

  • Options
    Hi Alex,

    I managed to achieve something similar using Python scripts, but it's not particularly elegant.

    For the Set column, I used a "Python script" generator with the following script -
    number_of_rows_per_set = 3
    
    def main(config):   
        return chr(ord('A') + (int(RowId - 1) - (int(RowId - 1) % number_of_rows_per_set)) / number_of_rows_per_set)
    

    For the Index column, I used a "Python script" generator with the following script -
    number_of_rows_per_set = 3
    
    def main(config):   
        return int(RowId - 1) % number_of_rows_per_set + 1
    
    You would need to change number_of_rows_per_set in each script and set the total number of generated rows to the number of sets multiplied by the number of rows per set.

    This works, but is a bit awkward. There might be an easier way of doing this that I haven't stumbled upon yet, but this might give you some ideas.
    Software Engineer
    Redgate Software
  • Options
    AlexMBanksAlexMBanks Posts: 15 Bronze 2
    Thanks for the response, Robert. This might help point us in the right direction.
Sign In or Register to comment.