In a loop how to indicate other column that [I]

gallardet

Hi.
Again I have to ask for help. I have written a script that calculates the percentage of change between the values ​​of a numeric column and the next one. Unfortunately I get an error: "Column 1897_Canvi Formula Interrupted
Cannot convert argument to a number [or matrix] 1 times At rows: {1} Operation: Subtract, numericColNames [X] - numericColNames [i]
Formula evaluation errors have been ignored "

This are the numericColNamesn:
{"_1842 _", "_ 1877_", "_1887_", "_1897_", "_1900_", "_1910_", "_1920_", "_1930_", "_1940 _", "_ 1950_", "_1960_", "_1970_", " _1981_ "," _1991_ "," _2001_ "," _2011_ "," _2018_ "}

This is the script in question:

Names Default To Here( 1 );
dt = Current Data Table();
numericColNames = dt << get column names( string, numeric );
For( i = 2, i <= N Items( numericColNames ), i++,
	Eval(
		Substitute(
				Expr(
					New Column( Char( numericColNames[i] ) || "_Canvi",
						Numeric,
						Continuous,
						Format( "Percent", 12, 1 ),
						X = i - 1,
						Formula( (numericColNames[X] - numericColNames[i]) / numericColNames[X] )
					)
				),
			Expr( __col__ ), Parse( ":Name (\!" " || numericColNames[i] || "\!")" )
		)
	)
);

txnelson

You are not setting up the Substitute() function correctly.

Substitute and Substitute Into
The Substitute() function returns a copy of a string with a replacement expression. The
Substitute Into() function changes the original string with a replacement expression.
For example, consider the following script:

str1 = str2 = "All things considered";
str3 = Substitute( str1, "All", "Some" );
// str3 holds the result of the Substitute, and str1 is not changed
str4 = Substitute Into( str2, "All", "Some" );
// Substitute Into returns nothing, so str4 is missing, and str2 is changed
// and now holds the result of Substitute Into
Show( str1, str2, str3, str4 );

This script returns the following output:
str1 = "All things considered";
str2 = "Some things considered";
str3 = "Some things considered";
str4 = .;
I have reworked you script, to what I am guessing will be what you want

Names Default To Here( 1 );
dt = Current Data Table();
numericColNames = dt << get column names( string, numeric );
For( i = 2, i <= N Items( numericColNames ), i++,
	Eval(
		Substitute(
				Expr(
					New Column( Char( numericColNames[i] ) || "_Canvi",
						Numeric,
						Continuous,
						Format( "Percent", 12, 1 ),
						X = i - 1,
						Formula( (__colX__ - __coli__ / __colX__ )
					)
				),
			Expr( __coli__ ), Parse( ":Name (\!" " || numericColNames[i] || "\!")" ),
			Expr( __colX__ ), Parse( ":Name (\!" " || numericColNames[X] || "\!")" )
		)
	)
);

The Substitute() function documentation was copied from the Scripting Guide

     Help==>Books==>Scripting Guide