The Query Tab

The Query tab allows you to provide a query that returns data from the model you select. The model data is returned as data rows in your dataflow's output. You can use the query builder provided, or you can create a custom query.
You can also select from or modify existing queries that are listed in the Query drop-down. If you modify existing queries and want to save those changes but also retain the original query, be sure to save the modified query under a new name; otherwise, you will overwrite the existing query. If you apply a different query to the model or cancel out of the application, changes to the query will be lost.
Follow these instructions to use the query builder:
  1. Choose whether you want to Select elements included in the query results, Add elements included in the query results, or Show result by highlighting the results on the canvas.
  2. Check the Include results from partial traversals box to include the results from each step in the query. Leave the box unchecked to include only the results that meet the requirements of the last step. For example, let's say that you are looking at a model that depicts world-wide terrorist activity for the events leading up to September 11, 2001, and you want to return data for any meetings that both Osama bin Laden and Mohamed Atta attended. Your query might include the following steps:
    • An initial step that includes an exact search type for entities with an _stp_id property that has a literal value of "Person:Osama bin Laden"
    • An Entity to Relationship step of connected with a relationship label of "Meeting"
    • A Relationship to Entity step of connected with a condition of an _stp_id property that contains "Atta"
    If you leave the Include results from partial traversals box unchecked, the query would return a single meeting between Osama bin Laden and Mohamed Atta. If you check the box, the query would return all meetings between Osama bin Laden and anyone else in the model. The additional records would be returned because the second step looks for meetings attended by Osama bin Laden and checking the box returns results from each step in the query.
  3. Leave the Include results from circular traversals box checked to include elements that occur more than once in each traversal. Uncheck the box to include those elements just once in each traversal. For example, let's say you are using the same model mentioned in step 1, and you initially want to return data for any meetings that Mohamed Atta attended but once you have those results, you want to see all attendees of a particular meeting. Your query might include the following steps:
    • An initial step that includes an exact search type for entities with an _stp_id property that has a literal value of Person:Mohamed Atta, which will return his entity
    • An Entity to Relationship step of connected with a relationship label of "Attended", which will return all events that he attended, including a meeting in Kandahar
    • A Relationship to Entity step of connected with a condition of an Event property that contains "Kandahar", which will return just the Kandahar meeting
    • An Entity to Relationship step of connected with a relationship label of "Attended", which will return relationships that connect to three other entities who attended the meeting in Kandahar and may or may not return the (already traversed) relationship that connects to Mohamed Atta, depending on whether you use this option.
    If you leave the Include results from circular traversals box checked, Mohamed Atta's relationship will be returned in addition to those for the other three attendees. If you uncheck the box, Mohamed Atta's relationship will not be returned because that relationship (Person:Mohamed Atta->Attended->Meeting:Kandahar) was already traversed in the first step of the query.
  4. Leave the Limit results to box checked and enter a number to specify the total maximum number of entities and relationships to return from the query. The default is 5000. The number entered here applies to unique elements, so if the same element appears in multiple results, they will count as one result. To avoid this scenario, use the dedup function discussed at the end of this topic; it will remove duplicate results in the output. If your root step returns a list and you are querying a large model, we strongly suggest entering a limit in this field to prevent the server from becoming unresponsive.
    Note: Limits can be set here or as Query Result Limits set in Relationship Analysis Client General Settings; if the limits are not the same, the lower limit will prevail.
  5. Complete the Selection tab.
    • Click All entities, All relationships, or Specify starting entities to identify what you want to query against. The Specify starting entities selection allows you to determine at what point in the model you want to begin your search. For instance, if you are looking at a model that depicts world-wide terrorist activity during specific years, you might have country names for entities. Rather than query against the entire model, you might want to look at activity just in Afghanistan. In this case you could select "All" as the Search type and "Country" as the Property name, leave Literal selected, and enter "Afghanistan" as the Property value.
      Note: The value in the Property value field is case sensitive if that field was indexed with the Exact type selected (versus Case insensitive). Click here for more information on selecting types when indexing fields.
      You could also click Field and select "Location" as the Property value, for example, rather than entering a specific value. If you select Field, an Input Data grid containing the name of the field you just selected will appear under the query name along with a cell where you can enter the default value. If you reuse this query elsewhere, you can use the default value you provided in this step, or you can override the default at that time.
    • If you clicked All entities or Specify starting entities, select the Entity types for your query. You can choose to query selected types or all types. Click Select None to de-select any selected types. In addition to returning a more focused set of results, selecting entity types will affect other factors such as which properties and fields are available in the first step of the query, which directions, entity types, and relationship labels are available in subsequent steps of the query, and so on.
    • If you clicked Specify starting entities, select the Search type:
      Exact Searches the index for data that matches exactly what you enter on the Selection tab, including casing. As with property values, the value here is case sensitive if that field was indexed with the Exact type selected; if case sensitivity was used and you search for "texas" while your data includes entries of "Texas", they will not be returned.
      Starts with Searches the index for data that contains text beginning with what you enter on the Selection tab. The search does not need to be a complete word. For example, a literal property value of “tech” or "tec" would be considered a match for a property value containing “Technical”, “Technology”, “Technologies”, or “Technician”.
      Ends with Searches the index for data that contains text ending with what you enter on the Selection tab. The search does not need to be a complete word. For example, a literal property value of "Emirates" or "tes" would be considered a match for a property value containing "United Arab Emirates".
      Contains Searches the index for data that contains the text that you enter on the Selection tab. The search does not need to be a complete word. For example, a literal property value of “Pitney” and “Pitney Bowes” would be considered a match for a property value containing “Pitney Bowes Software Inc.”
      Any Searches the index for data that contains any of the text that you enter on the Selection tab. For example, a literal property value of “Austin Tex” would be considered a match for a property value containing “Texarkana” or "Stephen F. Austin University".
      All Searches the index for data that contains all of the text that you enter on the Selection tab. For example, a literal property value of “Allstate claim 2013” would be considered a match for a property value containing “filed claim with Allstate June 2013”, as would literal property values of "all state" or "all 13".
      Between Searches the index for data that falls within a range that you specify on the Selection tab. When you use this search type, you must select a Property name that contains date, time, date/time, or numeric data. All numeric data types are supported with the exception of BigDecimal. For example, the following specifications would return all entities with StartDate values occurring in the year 2000:
      • A Property name of "StartDate" that is a Date type
      • A literal Start value of “1/1/2000"
      • A literal End value of "12/31/2000"
      Fuzzy

      Searches the index for the text you enter on the Selection tab but allows for some differentiation (missing letters, extra letters, or substitutions of letters). The amount of differentiation that is acceptable to still be considered a match depends on what you enter in the Metric field. This figure must be greater than zero and less than one; in other words, it must range from ".1" to ".9". For example, if you search for "Barton" and enter ".9" as the metric, the search will return records with "Carton" (replaces B with C), "Bartons" (adds s), and "Baton" (removes r), because all of these words are one character different from the search word "Barton".
      Wildcard
      Searches the index for the text you enter on the Selection tab but allows for a single wildcard character or a wildcard character sequence. Supported wildcards include the question mark (?), which matches any single character, and the asterisk (*), which matches any character sequence (including blanks). For example, if you search cities in Texas for "Aus*", the search will return records with "Austin", "Austonio" and "Austwell". If you conduct a similar search for "Aust??", only "Austin" will be returned because each question mark represents a single character and the other two city names have more characters in their name.
      Note: A query that includes an asterisk wildcard as the first character in the search string may result in a lengthy response time.
    • If you clicked Specify starting entities, select the Property name from the drop-down list. This list contains all properties associated with the entities and relationships that make up the model.
      Note: You can only query properties that have been indexed; non-indexed properties will not appear in the Property name drop-down.
    • If you clicked Specify starting entities, and selected a search type other than "Between", select the Property value. You can click Literal and enter a text string to be used in the search. Alternatively, you can click Field and select the field whose data should be searched; if you choose this option you will also need to enter a value in the Input Data grid.
    • If you clicked Specify starting entities, and selected the "Between" search type, select the Start value and End value to enter the range. You can click Literal and enter a value to be used in the search. Alternatively, you can click Field and select the field whose data should be searched; if you choose this option you will also need to enter a value in the Input Data grid.
  6. Complete the Conditions tab if you want to place additional constraints on the query. The Conditions tab has four entry fields:

    1. If you are creating the first condition, the Logical operator field will remain empty. If you are creating a subsequent condition, specify whether this condition should be used in conjunction with previous conditions ("And") or if it should be used instead of previous conditions ("Or").
    2. Select the property on which the condition will be based in Data source field.
    3. Select an operator for the condition that is appropriate for the data type in the Operator field:
      Equals
      Searches model elements for properties with values that match exactly what you enter in the Value field. This can be a numeric value or a text value.
      Not Equals
      Searches model elements for properties with values that have any value other than what you enter in the Value field. This can be a numeric value or a text value.
      Exists
      Searches the model elements for the existence of the property that you select in the Data Source field.
      Does not Exist
      Searches the model for elements that do not contain properties that you select in the Data Source field
      Is Blank
      Searches model elements for properties that contain no data. If a property value is blank, that element will be returned. This can be a numeric value or a text value.
      Is Not Blank
      Searches model elements for properties that contain any data. If a property value is not blank, that element will be returned. This can be a numeric value or a text value.
      Greater Than
      Searches model elements for properties whose values are greater than the value you specify. This can be a numeric, date, date/time, or time value.
      Greater Than or Equals
      Searches model elements for properties with numeric values that are greater than or equal to the value you specify. This can be a numeric, date, date/time, or time value.
      Less Than
      Searches model elements for properties with numeric values that are less than the value you specify. This can be a numeric, date, date/time, or time value.
      Less Than or Equals
      Searches model elements for properties with numeric values that are less than or equal to the value you specify. This can be a numeric, date, date/time, or time value.
      Contains
      Searches model elements for properties with values that contain what you enter in the Data Source field. The search does not need to be a complete word. For example, a literal property value of “Pitney” or “Pitney Bowes” would be considered a match for a property value containing “Pitney Bowes Software Inc.” This can be a numeric value or a text value.
      Does not Contain
      Searches model elements for properties that do not contain what you enter in the Data Source field. The search does not need to be a complete word. For example, a literal property value of “Pitney” or “Pitney Bowes” would be considered a match for a property value containing “Pitney Bowes Software Inc.,” so "Pitney Bowes Software"would not be returned. This can be a numeric value or a text value.
      Starts With
      Searches model elements for properties whose values start with the text you enter in the Data Source field. For example, if you enter "Van" for the LastName field it would return results with "Van Buren", Vandenburg", or "Van Dyck".
      Does Not Start With
      Searches model elements for properties whose values do not start with the text you enter in the Data Source field. For example, if you enter "Van" for the LastName field it would not return results with "Van Buren", Vandenburg", or "Van Dyck" but would return results with "Eddie Van Halen".
      Ends With
      Searches model elements for properties whose values end with the text you enter in the Data Source field. For example, if you filter for records that end with "burg" in the City field, it would return results with "Gettysburg", "Fredricksburg", and "Blacksburg".
      Does Not End With
      Searches model elements for properties whose values do not end with the text you enter in the Data Source field. For example, if you filter for records that end with "burg" in the City field it would not return results with "Gettysburg", "Fredricksburg", and "Blacksburg" but would return results with "Burgess".
      Match Regular Expression
      Searches the model elements for properties having a regular expression match for what you enter in the Data Source field. Regular expression matches identify strings of text of interest, such as particular characters, words, or patterns of characters. The value field should contain a valid regular expression pattern.
      Is Roughly Similar To
      Searches model elements for properties with values close to what you enter in the Data Source field but allows for some differentiation (missing letters, extra letters, or substitutions of letters). This operator is equivalent to the Fuzzy search type with search metric of .5.
      Is Similar To
      Searches model elements for properties with values close to what you enter in the Data Source field but allows for some differentiation (missing letters, extra letters, or substitutions of letters). This operator is equivalent to the Fuzzy search type with search metric of .6.
      Is Very Similar To
      Searches model elements for properties with values close to what you enter in the Data Source field but allows for some differentiation (missing letters, extra letters, or substitutions of letters). This operator is equivalent to the Fuzzy search type with Search Metric of .7.
    4. In the drop-down box following the list of operators:
      • Select Literal and enter a text string the fourth box (called the Value field) to be used in the query.
      • Select Field and then select the field whose data should be searched in the Value field.
      • Select a previous step (such as "Root" or "Step1") and then a property in the Value field to compare property values for the current step against values returned in a previous step. (Note that if you named the output on the Output tab of previous steps, those names will appear in the drop-down rather than "Root" or "Step1".) In this case, the properties shown in the Value field are based on properties for the previous step. For example, if you knew the name of one person (Mohamed Atta) who attended a particular event (a meeting in Kandahar) but wanted to know the names of the other attendees, you could create the following query that includes a property value comparison:
        • A root step that looks for an entity type Person with an _stp_id of that contains "Mohamed"
        • An Entity to Relationship step with a relationship label of "Attended"
        • A Relationship to Entity step with a condition that includes an Event that contains "Kandahar"
        • An Entity to Relationship step with a relationship label of "Attended" plus a condition that this step's _stp_id does not contain the same _stp_id value that was found in the root step.
        This query will find that Mohamed Atta attended an event in Kandahar and that it was also attended by three other people whose _stp_id value is not "Mohamed".
    5. Click Ignore Case if the query results can be either upper or lower cased.
    6. Repeat steps a through e to add additional conditions.
    7. Click OK.
    If, for example, you want to target terrorist activity in Afghanistan between 2001 and 2010, you would create two conditions. First, you would select "date" for the Property name, then "Greater Than or Equals", leave Literal selected, and then enter "2001". You would follow this with a second condition set to "And" that also uses "date," then "Less Than or Equals", then "2010". Alternatively, you could click Field and select "Date" rather than entering a specific value. Add, delete, or change the order of conditions by using the icons on either side of the conditions. Click Ignore Case if the query results can be either upper or lower cased.
  7. Complete the Output tab to define how you want your output to appear.
    • Click the Include in results box if you want the results from this step to be included in the output.
      Note: This box must be checked for the last step in any series; therefore, if there is only one step you cannot uncheck this box.
    • Click Specify name and enter text in the Name field to provide a name for this step in the output. Click List to use this entry as the name and type of the field in hierarchical output; leave it unchecked to have this entry added as a prefix for all output fields. Using the example from step 3, you might call this step "Afghanistan". Output fields from this step may be named "Afghanistan.Latitude" or "Afghanistan.Date".
    • Click Use type name to use the field type as the name for this step in the output. Entities will use entity types and relationships will use relationship labels. Continuing with the same example, output fields with this selection may be named "Person.Latitude" or "Person.Date". If you select this option and enter a name in the Name field, that name will also be added as a prefix for all output fields in addition to the field type. Continuing with the same example, output fields with this selection may be named "Afghanistan.Person.Latitude" or "Afghanistan.Person.Date".
  8. Specify the steps you want the query to take by selecting the appropriate option in the Add Operations drop-down. You can complete this step for the Flow, Conditions, or Output tab. Note that your options vary by whether the root element is an entity or a relationship.

    • If you choose Entity to Entity (valid for All entities and Specify starting entities), you can then refine your search to return data based on relationship label(s) between two entities (Connected), before entities (Predecessors), or after entities (Successors). For example, if you are querying a model of family members, and you choose a Relationship label of "Father," a Connected query will return all entities that have a Father label between them (in other words, fathers, sons, and daughters). A Predecessors query will return all entities who are a source entity of a Father relationship connected to another entity (in other words, fathers). A Successors query will return all entities who are the target entity of a Father relationship connected to another entity (in other words, sons and daughters).

      As in the root step of your query, you can also select Entity types for this step of the query. You can choose to query selected types or all types. Click Select None to de-select any selected types.

    • If you choose Entity to Relationship (valid for All entities and Specify starting entities), your options are very similar to those for Entity to Entity. You can refine your search to return data based on relationship label(s) that attach two entities (Connected), occur before entities (Predecessors), or occur after entities (Successors). You can also add conditions to and define output for the query.

    • If you choose Relationship to Entity (valid for All relationships), you can refine your search to return data based on conditions you set. You can return data when a condition is in place for an entity that is connected to another entity (Connected), an entity that is a source to a relationship (Predecessors), and for when an entity is a target of a relationship (Successors). As in the root step of your query, you can also select Entity types for this step of the query. You can choose to query selected types or all types. Click Select None to de-select any selected types.
    Regardless of the type of operation you add, you can create Conditions for that operation. You can also define how you want the output from this step to appear. You will notice that steps subsequent to the root step are given a path for output. The path and the step name define the hierarchy of your output data. If you checked the List box in the root step, this path will default to being part of the path in the step before it; however, you can remove the name of the root step. For example, if you named the root step "Locations" and clicked the List box, the first step would by default show "/Locations" in the Path field. (Alternatively, you could remove "Locations" and leave just the slash to have this step originate at the root.) If you called the first step "CountryName", the second step would by default show "/Locations/CountryName" in the Path field and the Locations field output would contain a list of CountryName results. Click the Include in results box if you want the results from this step to be included in the output. Click Dedup if you want the query to remove duplicate results from the output.
  9. Click OK.