Fill field column if other column is not empty

SanjshahSanjshah Posts: 7 Bronze 1
edited January 11, 2019 5:32PM in SQL Data Generator
Hi,

Is it possible to only complete a field if another column field is not empty?

I have managed from the documentation to find how to do this however I do have another question, is it possible to select a random value condition depending on the value?

random.choice('a','b','c') if Approved == "Yes" or Approved == "No" else "Awaiting"

Thanks in advance.




Tagged:
· Share on Twitter

Best Answer

  • Alex BAlex B Posts: 1,158 Diamond 4
    Answer ✓
    Hi @Sanjshah,

    SQL Data Generator uses IronPython and it appears this is still version 2.7 included with the install so a lot of newer functionality is not available (e.g. random.choices).  You may be able to use something like the following to get a random selection from the list foo
    import clr

clr.AddReference("System")
from System import Random
random = Random()

def main(config):
    # config["column_name"] is the column name
    # config["column_type"] is the column datatype
    # config["column_size"] is the column size
    # config["n_rows"] is the number of rows
    # config["seed"] is the current random seed
    
    foo = list('abcde')
    
    val = ''
    
    if (col2 == 1) or (col2 == 2): 
      val = foo[int(random.Next(len(foo)))] 
    else:
      val = 'awaiting'
      
    return val
    And it gives you output like this:

    ​

    I hope that helps!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter

Answers

  • sam.blackburnsam.blackburn Posts: 224 Gold 2
    Yes, you can do it using Python.  Here are some example scripts to solve similar sorts of problems:

    https://documentation.red-gate.com/display/SDG4/Example+Python+scripts
    Software Developer
    Redgate Software
    · Share on Twitter
  • SanjshahSanjshah Posts: 7 Bronze 1
    Many thanks Alex,

    That example is clear to understand.

    Alex B said:
    Hi @Sanjshah,

    SQL Data Generator uses IronPython and it appears this is still version 2.7 included with the install so a lot of newer functionality is not available (e.g. random.choices).  You may be able to use something like the following to get a random selection from the list foo
    import clr

clr.AddReference("System")
from System import Random
random = Random()

def main(config):
    # config["column_name"] is the column name
    # config["column_type"] is the column datatype
    # config["column_size"] is the column size
    # config["n_rows"] is the number of rows
    # config["seed"] is the current random seed
    
    foo = list('abcde')
    
    val = ''
    
    if (col2 == 1) or (col2 == 2): 
      val = foo[int(random.Next(len(foo)))] 
    else:
      val = 'awaiting'
      
    return val
    And it gives you output like this:

    ​

    I hope that helps!

    Kind regards,
    Alex


    · Share on Twitter
Sign In or Register to comment.