BookmarkSubscribeRSS Feed
amy

Contributor

Joined:

Apr 27, 2018

Check data in rows of one table to see if they are in columns of another

First table:

Capture.PNG

 

Second table:

Capture.PNG

 

I am trying to check the maximum values in the fourth row of 1st table against their adjacent columns from the second table. 

So far, I have extracted column names and row values from the first table:

dt1 = Open();
namesForCols = dt1 << get column names("String");
show(namesForCols);

row4 = dt1[4, 0];
show(row4);

close(dt1,nosave);

How can I check whether values in list row4 exist in columns of the second table? Thanks.

 

 

1 REPLY
Highlighted
txnelson

Super User

Joined:

Jun 22, 2012

Re: Check data in rows of one table to see if they are in columns of another

Here is a simple script that answers your question.

Names Default To Here( 1 );
dt1 = Data Table( "Table 1" );
dt2 = Data Table( "Table 2" );

// Get the numeric columns
colNamesList = dt1 << get column names( numeric, string );

// Add a new row of data, that will tell how many rows in
// Table 2 have the maximumn values from row 4
dt1 << add rows( 1 );
dt1:Column 1[N Rows( dt1 )] = "Number of Rows Found";

// Loop across the numeric columns in table 1
For( i = 1, i <= N Items( colNamesList ), i++,
	// If the column in table 2 exists, process
	If( Try( Column( dt2, colNamesList[i] ) << get name, "" ) != "",
		// Get the value from table 1 to compare with in table 2
		compareValue = Column( dt1, colNamesList[i] )[4];
		
		// Find the number of rows in table 2 that match the 
		// value in row 4 of table 1 and save it into row 5
		// of table 1
		Column( dt1, ColNamesList[i] )[N Rows( dt1 )] =
		N Rows(
			dt2 << get rows where(
				As Column( dt2, colNamesList[i] ) == compareValue
			)
		);
	)
);
Jim