357681_en-US

liliconcom

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

Hi,

https://community.jmp.com/t5/Discussions/JSL-how-to-create-a-reference-to-a-table-variable-inside-a/td-p/40102 

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])
  
));
<BR />//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 ?

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

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 ) );