Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- need help identifying first nonzero column

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Dec 13, 2017 8:06 AM
(864 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

@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

Uday

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Uday

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: need help identifying first nonzero column

Dec 13, 2017 9:02 AM
(839 views)
| Posted in reply to message from uday_guntupalli 12/13/2017 11:15 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

@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

Uday