cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
  • Learn how to build custom Python data connectors and further customize JMP’s Data Connector Framework with the Python Data Connector Demo, available now in the JMP Marketplace!
  • See how to create experiments to support product design and ID useful product features. Register for June 12 webinar, 2pm US Eastern Time.

Discussions

Solve problems, and share tips and tricks with other JMP users.
Choose Language Hide Translation Bar
dale_lehman
Level VII

need help identifying first nonzero column

I have a large dataset with around 30 columns of time-dated financial entries.  I want to know which column is the first with a nonzero (no columns have missing data, but I want to get the first column with a positive value entry) value.  I've attached a small made-up example with four time columns and a fifth column showing what I'd like my formula to result in.  If the zero values were missing, I could use the function for Number (number of nonmissing values) to get what I want, but when there is no missing data and zero values, I can't figure out how to automate identifying which column is the first that has a positive value.  Any ideas will be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
uday_guntupalli
Level VIII

Re: need help identifying first nonzero column

@dale_lehman
Couple of thoughts and solutions here to your problem . 

Approach 1 : Continue with JSL from previous solution and add it to table 

dt = Current Data Table();
Mat = dt << Get As Matrix;  
DesList = list(); 
for(i = 1 , i <= N Rows(Mat), i++, 
		Pos = Min(Loc(Mat[i,0])); 
		Insert Into(DesList,Pos);
   );

dt << New Column("Test",Numeric,Continuous,<< Set Values(DesList));

// Please note that there will be "." missing value if none of your columns have a positive value 

 

Approach 2 : Script the formula in JSL and apply it to the table - a little roundabout - but scaleable - an old but relevant example - will edit later in the day 

for(i = 1, i <= N Items(InvColNames), i++,
							If(i == 1,
									Stmt = ":" || InvColNames[i] || " >= 1 &" || ":" || InvColNames[i] || " <= InvLimit" ;
									,
									Stmt = Stmt || " & " || ":" || InvColNames[i] || ">= 1 &" || ":" || InvColNames[i] || " <= InvLimit"  ; 
							  ) ;
					   );
					   
					Eval( Parse( Eval Insert("\[ dt_Inp << New Column("Status_Inv",Numeric,Continuous,Formula(^Stmt^)); ]\" ) ) );



Approach 3 :  Manually do something like this - not a fan of this, but can't seem to find an alternative by myself - maybe others will have better thoughts 

If(
	:Column 1 > 0, "Column 1",
	:Column 2 > 0, "Column 2",
	"No Positive"
)
Best
Uday

View solution in original post

3 REPLIES 3
uday_guntupalli
Level VIII

Re: need help identifying first nonzero column

@dale_lehman

Maybe try this ? 

 

dt = Current Data Table();
Mat = dt << Get As Matrix;  
DesList = list(); 
for(i = 1 , i <= N Rows(Mat), i++, 
		Pos = Min(Loc(Mat[i,0])); 
		Insert Into(DesList,Pos);
   );
Show(DesList); 
Best
Uday
dale_lehman
Level VII

Re: need help identifying first nonzero column

Thank you - this indeed computes what I want, but I need a bit more help (due to my almost nonexistent JSL abilities).  I can see the correct calculations in the log window, but when I enter your script into the formula window for a new column, I get blank values.  The warning I get concerns the Show() function.  How do I get the resulting calculations to appear in the new formula column?

uday_guntupalli
Level VIII

Re: need help identifying first nonzero column

@dale_lehman
Couple of thoughts and solutions here to your problem . 

Approach 1 : Continue with JSL from previous solution and add it to table 

dt = Current Data Table();
Mat = dt << Get As Matrix;  
DesList = list(); 
for(i = 1 , i <= N Rows(Mat), i++, 
		Pos = Min(Loc(Mat[i,0])); 
		Insert Into(DesList,Pos);
   );

dt << New Column("Test",Numeric,Continuous,<< Set Values(DesList));

// Please note that there will be "." missing value if none of your columns have a positive value 

 

Approach 2 : Script the formula in JSL and apply it to the table - a little roundabout - but scaleable - an old but relevant example - will edit later in the day 

for(i = 1, i <= N Items(InvColNames), i++,
							If(i == 1,
									Stmt = ":" || InvColNames[i] || " >= 1 &" || ":" || InvColNames[i] || " <= InvLimit" ;
									,
									Stmt = Stmt || " & " || ":" || InvColNames[i] || ">= 1 &" || ":" || InvColNames[i] || " <= InvLimit"  ; 
							  ) ;
					   );
					   
					Eval( Parse( Eval Insert("\[ dt_Inp << New Column("Status_Inv",Numeric,Continuous,Formula(^Stmt^)); ]\" ) ) );



Approach 3 :  Manually do something like this - not a fan of this, but can't seem to find an alternative by myself - maybe others will have better thoughts 

If(
	:Column 1 > 0, "Column 1",
	:Column 2 > 0, "Column 2",
	"No Positive"
)
Best
Uday

Recommended Articles