Is it possible to export a leaf formula to SQL for decision trees? I think the answer is "no" but I thought I'd throw the question out for discussion to see I'm missing something.
Best,
M
Ah, the details. As far as I can tell, if one saves the leaf number formual to the data table, then open the formula depot and add formula column, the SQL code changes and it looks like it is now adding the leaf number in the code. Is this what they want? See below
/*
* SQL code generated by JMP v13.1.0
* From the following model(s):
* Column Leaf Number Formula
*/
( CASE WHEN ( `Viscera weight` IS NULL) OR `Viscera weight` < 0.1445 THEN ( CASE WHEN ( Rings IS NULL) OR Rings < 9 THEN ( CASE WHEN ( `Viscera weight` IS NULL) OR `Viscera weight` < 0.0865 THEN 1 ELSE 2 END ) WHEN ( Rings IS NULL) OR Rings < 10 THEN 3 ELSE 4 END ) WHEN ( `Viscera weight` IS NULL) OR `Viscera weight` < 0.2205 THEN ( CASE WHEN ( `Shell weight` IS NULL) OR `Shell weight` < 0.262 THEN 5 ELSE 6 END ) ELSE 7 END ) as `Leaf Number Formula`
Hey Mike,
I am with you I cannot find a way to directly bring the leaf formula to the formula depot and export to SQL. You can save the table with the leaf formula back out to a SQL database, but it will just be the leaf labels no formula behind the labels.
Pete
Maybe I do not understand the expectation, but when I publish formula from within decision tree to depot for SQL, it looks similar to the leaf number formula save to the data table. Are you looking to publish the leaf label formula?
MySQL
( CASE WHEN ( `Viscera weight` IS NULL) OR `Viscera weight` < 0.1445 THEN ( CASE WHEN ( Rings IS NULL) OR Rings < 9 THEN ( CASE WHEN ( `Viscera weight` IS NULL) OR `Viscera weight` < 0.0865 THEN 0.0351378309691251 ELSE 0.145922204598497 END ) WHEN ( Rings IS NULL) OR Rings < 10 THEN 0.1948805065947 ELSE 0.389157234827507 END ) WHEN ( `Viscera weight` IS NULL) OR `Viscera weight` < 0.2205 THEN ( CASE WHEN ( `Shell weight` IS NULL) OR `Shell weight` < 0.262 THEN 0.31468029565759 ELSE 0.39771639823993 END ) ELSE 0.481055444893282 END ) as `Prob(Gender==F)`, ( CASE WHEN ( `Viscera weight` IS NULL) OR `Viscera weight` < 0.1445 THEN ( CASE WHEN ( Rings IS NULL) OR Rings < 9 THEN ( CASE WHEN ( `Viscera weight` IS NULL) OR `Viscera weight` < 0.0865 THEN 0.844704080018137 ELSE 0.617370486124874 END ) WHEN ( Rings IS NULL) OR Rings < 10 THEN 0.496329846162217 ELSE 0.291447737498427 END ) WHEN ( `Viscera weight` IS NULL) OR `Viscera weight` < 0.2205 THEN ( CASE WHEN ( `Shell weight` IS NULL) OR `Shell weight` < 0.262 THEN 0.254384209103701 ELSE 0.0992835838414273 END ) ELSE 0.0312668233994332 END ) as `Prob(Gender==I)`, ( CASE WHEN ( `Viscera weight` IS NULL) OR `Viscera weight` < 0.1445 THEN ( CASE WHEN ( Rings IS NULL) OR Rings < 9 THEN ( CASE WHEN ( `Viscera weight` IS NULL) OR `Viscera weight` < 0.0865 THEN 0.120158089012738 ELSE 0.236707309276629 END ) WHEN ( Rings IS NULL) OR Rings < 10 THEN 0.308789647243084 ELSE 0.319395027674065 END ) WHEN ( `Viscera weight` IS NULL) OR `Viscera weight` < 0.2205 THEN ( CASE WHEN ( `Shell weight` IS NULL) OR `Shell weight` < 0.262 THEN 0.430935495238709 ELSE 0.503000017918643 END ) ELSE 0.487677731707284 END ) as `Prob(Gender==M)`, /*ERROR:*/IfMax( `Prob(Gender==F)` ,'F', `Prob(Gender==I)` ,'I', `Prob(Gender==M)` ,'M','') as `Most Likely Gender`
JSL verion of Leaf Number Formula from Saved Column Formula:
If( Is Missing( :Viscera weight ) | :Viscera weight < 0.1445,
If( Is Missing( :Rings ) | :Rings < 9,
If( Is Missing( :Viscera weight ) | :Viscera weight < 0.0865,
1,
2
),
If( Is Missing( :Rings ) | :Rings < 10,
3,
4
)
If( Is Missing( :Viscera weight ) | :Viscera weight < 0.2205,
If( Is Missing( :Shell weight ) | :Shell weight < 0.262,
5,
6
7
JSL version seems more compact than SQL version
Chris
The request is:
"Can you remind us how to export Decision Tree logic into SQL so that it generates the Leaf Number?I was able to publish the modeling formula, but the SQL is generating predicted rates - as opposed to Leaf Number which is what we need."
So they need the actual leaf number - not a prediction equation for the response variable.
