Exclude specific rows from your data

January 12, 2017 23:23

Note: The main section of this 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 information in the comments relates to using Python.

To exclude specific records from your data, some useful functions are the following:

where - A keyword commonly used in conjunction with emit to control the output based on a condition.
execCount - A numerical variable (data type int) that can be used to provide a count of the number of records in a data set.
firstExec - A boolean variable which has a value of true for the only the first record.

Example: Exclude the first record in the data shown below.

Using the where clause:

emit *
where 'color' != "Red"

Using execCount:

emit *
where execCount != 1

Using firstExec:

emit *
where not firstExec

Comments

3 comments

Nick Lancaster

March 28, 2018 04:37
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
Adrian Williams

March 28, 2018 14:26
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
Adrian Williams

March 28, 2018 14:47
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.

Comments

Didn't find what you were looking for?