Exclude specific rows from your data

Comments

3 comments

  • Avatar
    Nick Lancaster

    How do you exclude rows based on use defined date?

    In MS Access queries by putting [Enter text here] in the where statement when the query is runs a input box pops up allowing the user to enter the required value.

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    Hi Nick,

    Firstly, a caveat on the content of the main article: The article is only relevant if you are using our legacy scripting language (BRAINScript). We recommend using the new Tranform node (and derivative nodes) that support Python.

    The simplest way of excluding records from your data with a user-defined date is to use the Filter node - which offers a code-free solution.

    You can also filter data from within the Data Viewer. If you click on the context menu in a column header it shows the available menu options, including the Filter option:

    The Create <fieldtype> Filter dialog is displayed which offers a range of criteria applicable to the data type of the selected column. As the 'dueDate' field has a date type, it offers filters such as 'Before' or 'After' and a date-picker for the value. You can also enter the date manually with a format of CCYY-MM-DD.

    When the filter is applied the viewed records are updated to reflect the active filter and the column menu icon changes to indicate the column is being filtered. You can persist this filter in the dataflow by using the 'Add Nodes to Data Flow' option on the Data Viewer's menu:

    You can add a Filter node or a Split node to the canvas, as required.

     

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    If you are interested in using custom filters using Python in the Transform node, you may want to look at this article:

    https://support.infogix.com/hc/en-us/articles/360018962334-Using-the-Python-based-Transform-Node 

     

    To echo the example filter discussed above, you would add a Transform node to the canvas and configure it as follows (omitting the [Code] and [/Code] delimiters). The ConfigureFields property script sets up the output record metadata to be the same as the input data and defines the value for the threshold date. It is useful to define the value here once as it is a constant value that applies to the processing of all the records.

    [Code]

    out1 += in1

    TestDate = '2016-01-01'

    [/Code]

     

    The ProcessRecords property script is configured to set the match condition to False if the input data value is Null or the date value is before the threshold date. Only records that match the criterion are output@

     

    [Code]

    if in1['dueDate'] is Null:
      Match = False
    elif in1['dueDate'] > datetime.datetime.strptime(TestDate, '%Y-%m-%d').date():
      Match = True
    else:
      Match = False

    if Match:
      out1 += in1

    [/Code]

     

    There are some other examples of using Python with date/datetime values in the Tips & Tricks section of the forum:

    https://support.infogix.com/hc/en-us/sections/360003534293-Tips-Tricks 

    0
    Comment actions Permalink

Please sign in to leave a comment.



Powered by Zendesk