JSL: How to loop through columns, generate a Graph Builder report, and save each as a JPG file

abdulj

I'm trying to create a script that can loop through each cell of each column to count the number of cells that are less or greater than a specified value.

 

If i have a table of columns A ~ D and Rows 1 ~ 10, I'd like to count the values of the cells row-wise and column-wise. In other word, for all rows of column A, how many meet my criteria. And similarly for all columns of Row 1, how many meet my criteria. I would then do this for all rows and columns.

 

Here's what ive tried to start with, but it returns nothing. Any ideas on this or a more effcient way to do it?

 

 

Names Default To Here( 1 );

dt = Current Data Table();
Clear Log();


// Get all of the numeric columns in the data table
Numeric Columns = dt << get column names( numeric, string );
NRows = N Rows( dt );

Count = {};

//Column loop
For( i = 1, N Items( Numeric Columns ) <= 1, i++, 
	
	For Each Row(
		dt, 

		If( Column(dt, Numeric Columns[i]) < 101, 
			
			Count = Count++;
			Show( Count );
		) 
		 	
	)
		
	
);

 

Melanie_J_Drake

There are a few problems with the script that are easily fixed.

 

1. The test portion of the for loop is wrong: 

<SPAN class="token function">N Items</SPAN><SPAN class="token punctuation">(</SPAN> Numeric Columns <SPAN class="token punctuation">)</SPAN> <SPAN class="token operator"><=</SPAN> <SPAN class="token number">1</SPAN>

 The number of Numeric columns doesn't change. If there are 0 or 1 numeric columns, the loop will run forever because 0<=1 and 1<=1. If there are at least 2 numeric columns, then the loop doesn't run at all because 2(or more)<=1 is always false. The test should be this:

 i <= N Items( Numeric Columns )

That tests whether your iterator (i) is greater than the number of columns and stops when it is.

 

2.  In a For Each Row loop, you still need to subscript into the row, you just don't have to manage the iteration yourself. So this piece:

<SPAN class="token function">Column</SPAN><SPAN class="token punctuation">(</SPAN>dt<SPAN class="token punctuation">,</SPAN> Numeric Columns<SPAN class="token punctuation">[</SPAN>i<SPAN class="token punctuation">]</SPAN><SPAN class="token punctuation">)</SPAN> <SPAN class="token operator"><</SPAN> <SPAN class="token number">101</SPAN>

Should look like this:

<SPAN class="token function">Column</SPAN><SPAN class="token punctuation">(</SPAN>dt<SPAN class="token punctuation">,</SPAN> Numeric Columns<SPAN class="token punctuation">[</SPAN>i<SPAN class="token punctuation">]</SPAN><SPAN class="token punctuation">)[Row()]</SPAN> <SPAN class="token operator"><</SPAN> <SPAN class="token number">101</SPAN>

 3. Your Count variable is a list, but you're treating it like a number. Count++ doesn't do anything to a list that isn't populated with numbers. Set Count = 0 instead of Count = {}. Additionally, Count++ returns the current value, and then sets Count to Count +1. So Count = Count++ will never change the value of Count. (Example: Count = 0. Count = Count++. Count is still equal to zero.)

 

Here is the full script with the changes above. The end Count is the number of cells in all numeric columns that are less than 101.

 

Names Default To Here( 1 );

dt = Current Data Table();
Clear Log();


// Get all of the numeric columns in the data table
Numeric Columns = dt << get column names( numeric, string );
NRows = N Rows( dt );

Count = 0;

//Column loop
For( i = 1, i <= N Items( Numeric Columns ), i++, 
	
	For Each Row(
		dt, 

		If( Column(dt, Numeric Columns[i])[Row()] < 101, 
			
			Count++;
			Show( Count );
		) 
		 	
	)
		
	
);

HTH,

Melanie