Subscribe Bookmark RSS Feed

Problem of extract data

blue1994

Contributor

Joined:

Feb 27, 2017

Hi, all

Sorry for disturbing,
i now need create the new column, i can create the column but now i face the problem is inside the column there, there is a formula. Then now i dunno how to exact the parameters and the target value from excel file. (meant here: -0.438 and 0.717(target) and the(parameter) I_P2A GSVT .6/.045 and I_N2A GSVT.3/.045) i need pull the target value and parameters from the excel file that i created without typing the formula inside. So does it possible to make it?(attachment below).

 

But now I have faced two problems:

First Problem:

 

TargetVal = dtExcels:Target[dtExcels << Get Rows Where( dtExcels:Parameters  == "I_P2A GSVT .6/.045 " )];
TargetVal = dtExcels:Target[dtExcels << Get Rows Where( dtExcels:Parameters  == "I_N2A GSVT .3/.045" )];

When i running these two sentences together, it will comes out error. But then when i run the sentence separately, the first sentence can find the target value which is from excel file, but the second sentences it cannot find the target value. It comes out empty value{}.

It will show this error for both sentences when I run together:

Expression does not fit available column types{1} in access or evaluation of 'Bad Argument' 

 

Second Problem:

When i replace the formula with this:  Formula((_TheNames_) - (_TheValue_) /((_sigma_) * (_TheNames_)),

Then it will shown me this error:

Column NEW_I_P2A_GSVT Formula Interrupted
Cannot mix lists and matrices 1 times At rows: 2 Operation: Multiply, [0.0231] * /*###*/{"I_P2A GSVT .6/.045 "} /*###*/
Formula evaluation errors have been ignored

And the second names for I_N2A GSVT.3/.045, the script cannot execute anything. I think is caused by the first problem.

 

 

Thanks.

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

I think this is what you are trying to do:

Names Default To Here( 1 );
dt_raw_dataset1 = Current Data Table();
dtExcels = Open( "C:\Users\pkh\Desktop\Imat_UI\Book1.xlsx", "invisible" );

For( i = 1, i <= N Rows( dtExcels ), i++, 
	Eval(
		Substitute(
				Expr(
					Try( dt_raw_dataset1 << Delete Column( __ColName__ ) );
					Try( dt_raw_dataset1 << New Column( __ColName__,
						,
						Numeric,
						"Continuous",
						Formula( (__TheNames__) - (__TheTarget__) / ((__TheSigma__)) * (__TheNames__) ),
						Set Selected
					));
				),
			Expr( __ColName__ ), "NEW_" || Char( Word( 1, dtExcels:Parameters[i], " " ) ),
			Expr( __TheNames__ ), Parse( ":name(\!"" || dtExcels:Parameters[i] || "\!")" ),
			Expr( __TheTarget__ ), dtExcels:Target[i],
			Expr( __TheSigma__ ), dtExcels:Sigma[i]
		)
	)
);
Jim
1 REPLY
txnelson

Super User

Joined:

Jun 22, 2012

Solution

I think this is what you are trying to do:

Names Default To Here( 1 );
dt_raw_dataset1 = Current Data Table();
dtExcels = Open( "C:\Users\pkh\Desktop\Imat_UI\Book1.xlsx", "invisible" );

For( i = 1, i <= N Rows( dtExcels ), i++, 
	Eval(
		Substitute(
				Expr(
					Try( dt_raw_dataset1 << Delete Column( __ColName__ ) );
					Try( dt_raw_dataset1 << New Column( __ColName__,
						,
						Numeric,
						"Continuous",
						Formula( (__TheNames__) - (__TheTarget__) / ((__TheSigma__)) * (__TheNames__) ),
						Set Selected
					));
				),
			Expr( __ColName__ ), "NEW_" || Char( Word( 1, dtExcels:Parameters[i], " " ) ),
			Expr( __TheNames__ ), Parse( ":name(\!"" || dtExcels:Parameters[i] || "\!")" ),
			Expr( __TheTarget__ ), dtExcels:Target[i],
			Expr( __TheSigma__ ), dtExcels:Sigma[i]
		)
	)
);
Jim