Repeat a set of values per another column generated

LuisQ

HI,
I've one table that a want to generate some that the stores a set of states by a key (another column). 
i.e. to each new key generated I want to insert 4 states, e,g,
Key | Value
1 | 1
1| 2
3| 3
4| 4
2 | 1
2| 2
2| 3
2| 4
3|| 1
3| 2
3| 3
3| 4

so I need to generate 200.000 keys with 4 states, a total of 800.000 rows. I know how to do it using T-SQL, but because it's the table has other columns that I need to generate I want to use the data generator and use this generation as part of a project.

As a general question: How can I generate new columns implementing rules that depend on other columns in the same Table?

Thanks very much, Luis

Alex B

Hi @LuisQ ,

You'll need to use a Python script generator to accomplish this, to be able to access the values of another column, though depending on what the keys and state actually are you might be able to do both with a Python script generator.

I've just replicated your example using the following though I understand it is an example and may not be numbers as easily produced as this:

Python script generator for "key" column defined as:

<div>def main(config):
<span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp;&nbsp;
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp; mySet = []
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp;&nbsp;
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp; for x in range(1,200000):
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp; &nbsp; &nbsp; for y in range(4):
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; mySet.append(x)
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp;&nbsp;
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp; return mySet
</span></div>

Python script generator for "state" column defined as:

<div>def main(config):
<span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp;
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp; &nbsp; return [1, 2, 3, 4]</span></div>

To reference another column in a python script you just type the name of the column (or use the "Insert Column Name..." button below the Script box.  For this, you would need to return a set or list of values (like the top example) rather than have a loop return one value at a time

I hope that helps!

LuisQ

Excellent  @Alex B, thanks for your quick and valid reply, it works fine... so it means that I can use always Python to create rules that relate columns in the same table, right? example if column A = 1 then column B = "AAAA" else  column B = "BBBBB" end. do you have any example? Sorry I'm very new using the generator
Cheers, Luis

Alex B

Hi Luis,

You could do that sort of thing with if/elif/else in Python on the Column B generator.  It would look something like:

def main(config):

    if key == 1:
        return 'AAA'
    elif key == 2:
        return 'BBB'
    else:
        return 'CCC'

The elif is only if you have more than one thing you want to specify a value for and the else covers all the others.

There are a lot of resources on writing code in Python (for example w3schools https://www.w3schools.com/python/python_conditions.asp ) and they should help with writing Python scripts for this generator.

Kind regards,
Alex