topic Parsing strings creating filter condition from excel and applying to it to datatable in Discussions

Parsing strings creating filter condition from excel and applying to it to datatable

joshua — Sat, 10 Jun 2023 23:25:56 GMT

Hi,

I would like to build custom formula sheet that I can modify and apply to data table.

Say have a sheet-like in the attachment.

Formula Sheet

and would like concatenate this formula so that I can apply to selectwhere.

I come with for loop idea below but I cannot figure it out how to parse it to selectwhere

dt = open("$SAMPLE_DATA/Cars.jmp")


formula_Sheet = open("C:\Formula_Sheet.xlsx",
	
	Worksheet Settings(
                                            1,
     Has Column Headers( 1 )
	
));


condition_list = {}

For( i = 1, i <= Nrows( formula_Sheet ), i++,
	
	Make_col = formula_Sheet:Name("column_name")[i] ;
	filter_state = formula_Sheet:Name("filter_state")[i] ;
	filter_1 = formula_Sheet:Name("filter_1")[i] ;
	
	combined_filter_cond[i] = select where(:Name(\!""||Make_col[i]||"\!")"\!||Filter_state"\!||Filter_1"\!)";
	
	condition_list = insert(combined_filter_cond[i])
		
));

//This is the final selection code should look like
dt << selectwhere((contains(:Name("Make"), "Honda") | !contains(:Name("D/P"), "Driver") | :Name("doors")>=2))

I would appreciate if admins can help on this ?

Re: Parsing strings creating filter condition from excel and applying to it to datatable

txnelson — Wed, 10 Feb 2021 05:33:49 GMT

Here is my rework of your script, to generate the selections from the cars data table.

names default to here(1);
dt = open("$SAMPLE_DATA/Cars.jmp");


formula_Sheet = open("C:\Formula_Sheet.xlsx",
	
	Worksheet Settings(
                                            1,
     Has Column Headers( 1 )
	
));
//formula_Sheet =data table("DATA_for_SAS_community");

theExpr = "dt << selectwhere(";

For( i = 1, i <= N Rows( formula_Sheet ), i++,
	If( formula_Sheet:column_name[i] != "",
		If( i > 1,
			theExpr = theExpr || "| "
		)
	);
	If( Contains( formula_Sheet:filter_state[i], "contain" ),
		theExpr = theExpr || formula_Sheet:filter_state[i] || "s(:Name(\!"" || formula_Sheet:column_name[i] || "\!"), \!"" || formula_Sheet:Filter_1
		[i] || "\!")",
		theExpr = theExpr || ":Name(\!"" || formula_Sheet:column_name[i] || "\!") " || formula_Sheet:filter_state[i] || formula_Sheet:Filter_1[i]
	);
);

theExpr = theExpr || ");";

Eval( Parse( theExpr ) );

Re: Parsing strings creating filter condition from excel and applying to it to datatable

joshua — Wed, 10 Feb 2021 06:21:01 GMT

Hi Jim,

This is the exactly solution that I'm looking for. Thank you very much coming and providing solution for this late in the night. You are a true expert!