Data360 Analyze

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

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.

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.

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