cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
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