We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Comments

5 comments

  • Avatar
    Christina Costello

    Hi Geoff, you're right...thanks for making us aware of the error. The cheat sheet shouldn't be using `is None` as it's relating to using the Transform node rather than the Python node. So I've updated the cheat sheet.

    Regarding your question on the Modify Fields node: should the Default value settings populate the field if its Null?

    No, Null is only populated if there is an error in calculating the output field, and if the WriteErrorRecordsToMainOutput is set to True. Attached is an example data flow which has the following...

    1. Drop a Create Data node and populate with, then run it:
    myNumber:string
    12e4/*-2
    456
    123
    2. Drop and connect up a Modify Fields node:
    • In the "Output Fields" grid, change the type of the "myNumber" field to "int"
    • Set a value in DefaultValueForNumber, e.g. 1111
    • Set WriteErrorRecordsToMainOutput to True
    3. Run the node and you'll see the output with the invalid value replaced, because there was an error converting from string to int:
    mylong
    1111
    456
    123

     

     

    0
    Comment actions Permalink
  • Avatar
    Christina Costello

     

     

    Attached files

    Modify Fields Example - 20 Feb 2020.lna

     

    0
    Comment actions Permalink
  • Avatar
    Geoff

    Ahh ok. would be cool if there was an "If Null" default :)

     

    Thanks for fixing the cheat sheet!!

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    You can use the Calculate Fields node to set a default value if a field is Null. 

    For example, the default data in the Create Data node includes Null values in a number of fields:

    The data quality bars in the field header area indicate the data is not complete.

    In the Calculate Fields node define calculated fields with the same name and data type as the input field. Define the Expression for the calculated field to be the input field.

    As the name of the calculated field already exists in the input data, the existing field is replaced by the calculated value. For records where the field is not Null, the data is effectively unchanged. However, if the field value is Null, the default value *for that data type* is substituted into the output field. The default value is set using the Optional DefaultValueFor... properties:

    Note that only a single default value can be defined for each data type in a particular node and will be used for all calculations that include fields with that data type. If you want to have different defaults for different fields then you can use multiple Calculate Fields nodes in series - specifying the required default values and corresponding input fields in each node.

    Here is the effect of applying the expressions shown above:

    As no default values were defined in the node's properties, the default values are used (False for Boolean fields, the epoch for dates and datetime fields and 00:00:00 for time fields). Input fields that are not specified in the node are passed through unchanged.

    0
    Comment actions Permalink
  • Avatar
    Geoff

    Ahh Perfect! Thanks Adrian!!

    0
    Comment actions Permalink

Please sign in to leave a comment.