How to compare the values between 2 dataview columns?

The Gateway-SQL plugin can be used to find similar values, exact values or values not in another dataview’s column.

Depending on the number of table cells it needs to compare between the 2 columns, this type of query may be expensive in terms of performance and may take some time to run.

Due to the above reason, you may consider configuring the sampler on purpose NOT to sample on startup and NOT to run on its own by setting the sampling interval to 0. To run the sampler manually, click on the Sample now or refresh button in the dataview.

To illustrate, if I have the following Table1 and Table2 Toolkit dataviews:

List the values in the NAME column of Table1 which contain the values from the name column of Table2 Copied

The query can be configured as:

select s.*, e.* from Table1 AS s, Table2 as e
where s.NAME like '%' || e.name || '%'

The matching values are shown in the below dataview:

The preceding dataview shows all table values from Table1 which the NAME column contains the value of name column from Table2. For example, NAME=adapterSF123abc contains name=adapterSF.

List the values in the NAME column of Table1 which match exactly with the values from the name column of Table2 Copied

The query can be configured as:

select s.*, e.* from Table1 AS s, Table2 as e
where s.NAME = e.name

The matching values are shown in the below dataview:

List the values in the name column of Table2 which do not show up in the NAME column of Table1 Copied

The query can be configured as:

SELECT * from Table2
WHERE NOT EXISTS
(select * from Table1
where NAME like '%' || Table2.name || '%')

The non-matching values are shown in the below dataview:

For more information, see the Gateway-SQL section in the Gateway Reference Guide. The SQL statements are based on SQLite.