Creating a filter to display related data

You can create a drop down list of values that when selected displays related information.

To do this, you have to:

  • Build a table component,
  • Build a user input control component, and
  • Create ARRAY and SELECT statements.

Step 1: Building a table component

  1. Select Build a New Klip.
  2. Select the Table component.
  3. Select either the use an existing data source or create a new data source option.
  4. Click Save.
  5. Name the Klip.
  6. Click Finished.

Step 2: Building and configuring a user input control component

  1. Select Build a New Klip.
  2. Select the User Input Control component.
  3. Select either the use an existing data source or create a new data source option.

Goal: Create display options for the drop-down list and values.

  1. From the component tree, select Klip, and type a Klip Title.
  2. From the component tree, select Drop-Down List.
  3. At the Properties panel, provide the required information:

Field

Required information

For our example

Control Label Enter a name for the drop down list. Claim_ID
Create New Variable Enter the name of the column header to filter data. Claim_ID
Default Value Enter a name for the default value. All

Goal: Create an ARRAY statement.

Location

Action

For our example

From the component tree select Values  
From the data source click the column header to filter data A:A
  click Wrap current value in function, ARRAY ARRAY( A:A, data)
In the formula editing toolbar, ensuring the column name to filter data is still selected click Insert empty node before current selection ARRAY( data , A:A , data )
Ensuring the first occurrence of data is still selected click Insert literal string or number, type All, Apply ARRAY( “All”, A:A , data )
If the column has a row header, ensuring the column name to filter data is still selected click Wrap current value in function, Data Manipulation, SLICE ARRAY( “All”, SLICE( A:A, start, end) , data )
In the formula editing toolbar click ARRAY, Evaluate Current Selection to verify that the formula is working correctly ARRAY( “All”, SLICE( A:A) , data)
Result: All, 100, 200, 300, 400, 500
  click Save  
  Name the Klip  
  click Finished  

Step 3: Connecting the user input control component with the table component

Goal: Create SELECT statements for the other columns of data.

Location

Action

For our example

For the table component click Edit Klip  
From the Properties Panel name and set the properties for the other columns to be displayed claim date (date), number of vehicles (number), and claim status (text)
From the component tree select a Column Column: claim date
From the Data tab, click Select data or start formula ... select the column header for the first related data column B:B
If the column has a row header, in the formula editing toolbar, ensuring the column is still selected click Wrap current value in function, Data Manipulation, SLICE SLICE( B:B, start, end )
In the formula editing toolbar, select SLICE click Wrap current value in function, Data Manipulation, SELECT SELECT( SLICE( B:B, start, end), ( condition ) )
With (condition) selected click Insert expression in parentheses (...)  
  click Insert variable and select the variable previously created SELECT( SLICE( B:B ) , ( (Claim_ID ... ) ... ) )
  click the first occurrence of the ellipses ...  
  click the = operator  
From the data source select the column header that is to filter data SELECT( SLICE( B:B ) , ( ( Claim_ID = A:A ... ) ... ) )
If the column has a row header, in the formula editing toolbar, with the column selected click Wrap current value in function, Data Manipulation, SLICE  
  Click the first opening bracket for the column header that is to filter data SELECT( SLICE( B:B ) , ( ( Claim_ID = SLICE( A:A ) ... ) ... ) )
  select Wrap current value in function, Logic, OR SELECT( SLICE( B:B ), OR( ( ( Claim_ID = SLICE( A:A ) ... ) ... ) , condition ) )
  click condition  
  click Insert expression in parentheses  
  click Insert variable and select the variable for the column header to filter data SELECT( SLICE( B:B ), OR( ( ( Claim_ID = SLICE( A:A ) ) ), ( Claim_ID ... ) , condition ) )
  click the first occurrence of the ellipses ...  
  click the = operator  
  click Insert literal string or number  
  type All and click Apply SELECT( SLICE( B:B ), OR( ( ( Claim_ID = SLICE( A:A ) ) ) , ( Claim_ID = “All” ...) , condition ) )
In the formula editing toolbar click SELECT, click Evaluate Current Session to verify the formula is working correctly Result: 25/04/2013, 26/05/2013, 27/08/2013, 28/09/2013, 29/10/2013
  copy and paste this formula to all other columns you want to display, changing the first column reference to that column. Use Ctrl+C and Ctrl+V
  click Finished  

Feedback and Knowledge Base