Is it possible to Filter out some data when adding new datatable from existing datatable

[Ram Kumar]

For Example: Ihave a datatable A with500 records for countries India,US,Canada..I wantto add another datatable from existing datatable A...Now i want data only for India in the second datatable..Is there a way

[Rohit Chougule]

Hello,

You can have the data filtered depending on a specific column value.

You can achieve it using Data on Demand option from the Data table Properties, you can find a detailed description of the "Data on Demand" from the below URL:

 

https://docs.tibco.com/pub/sfire-analyst/7.10.1/doc/html/en-US/TIB_sfire...

Hope it helps.

Best Regards,

Rohit Chougule

[Ram Kumar]

Hi Rohit, I should have mentioned earlier, datatable A datasource is Excel flat file...On-demand option disabled because of that..

 

[[{"fid":"125976","view_mode":"default","attributes":{"height":"646","width":"561","class":"media-element file-default"},"fields":{"format":"default","field_file_image_alt_text[und][0][value]":"","field_file_image_title_text[und][0][value]":""},"type":"media"}]]

[Khushboo Rabadia]

There is no direct functionality to apply filter while fetching records from existing datatable. You can have below workaround.

Step1: Create a calculated column say countrycalc for filter like case when [country]="India" then 1 end

Step 2: Add data table from existing analysis and then add pivot transformation in it as shown below with onlycountrycalc column in aggregation. Then do insert columns from that datatable A to datatable B, pull all the required columns and do left outer join. This will bring records only where country=India.

1. Select File > Add Data Tables...

Source: Data table from current analysis

Data table: Country

Update behavior: Automatic

Added transformations

Transformation name: Pivot

Row identifiers: (None)

Value columns and aggregation methods: First(countrycalc)

Column titles: (None)

Column naming pattern: %V

Transfer columns and aggregation methods: (None)

Transfer column naming pattern: %A(%T)

2. Select Insert > Columns...

Source: Data table from current analysis

Data table: Country

Update behavior: Automatic

Matching behavior: Tries to match the specified columns when data is loaded.

Matched columns: countrycalc countrycalc

Ignored columns: (None)

Join method: Left outer join

Treat empty values as equal: No

[Ram Kumar]

Thanks Khushboo :)

[Atheer Al Attar]

you can easily do this using TERR, let's assume I want to split my main table into 5 sub tables where column (City) is a different value each time:

1- Add a function Tools--->ResgisterFunction

Script:

t1

[Tymoteusz Grzybowski]

outstanding trick!

[Khushboo Rabadia]

Now filter rows transformation is available starting 10.1 so no need for any workarounds you can directly apply filter in transformation

[gurusai sankar]

Hi Khusboo,

 

Request your help below spotfire query.

 

 

 

https://community.spotfire.com/questions/how-create-dynamic-check-and-unchecked-filters

 

 

[Chad Holland]

I do this, and no matter what I try the function runs but does not actually return anything into the new table it creates. I have made sure that the filter value I am using is in the table and column I am referencing. Note, if I use 'NAME'=="something" I get an error that says object NAME cannot be found, though this column is in my input table. I also tried 1=="something" and "NAME"=="something". These do not return an error, and the function will create the new table, but the new table does not have any columns/rows.Note, I am trying to do a simple filter on one table based on a document property the user selects from a drop down. The filtered rows will then be output to a second table that will be used in Lines and Curves. My goal is to use a property control that will update the document property, when this property is updated it will feed that value to the data function which will use that to filter my main table and output only those rows where the NAME = document property.

 

Using Filters built into spotfire is not an option here as I need to create 3 tables from this master table and these 3 tables must only contain those rows where the NAME value equals the single selection.Any thoughts