BookmarkSubscribeRSS Feed
BSwid

Occasional Contributor

Joined:

Sep 25, 2018

Import data from Excel from SAP, how to fill in blanks with the value above?

Often there are data tables in Excel or from SAP that do not repeat values down a column.  This is not missing data.  The data is implied from the last cell in the column that has a value.   Is there a way to get JMP to fill in my imported data with those values?

 

JMP Question Fill in Blanks.png

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
stan_koprowski

Community Manager

Joined:

Aug 8, 2012

Solution

Re: Import data from Excel from SAP, how to fill in blanks with the value above?

Hi @BSwid,

Highlight the values and right-click and select Fill.

(view in My Videos)

 

 

txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: Import data from Excel from SAP, how to fill in blanks with the value above?

This little script should do what you want

names default to here(1);
dt = current data table();
for(I=2, I<=N rows(dt),  I++,
     if(:room[I] == "", :room[I] = :room[I-1]);
);
Jim
BSwid

Occasional Contributor

Joined:

Sep 25, 2018

Solution

Re: Import data from Excel from SAP, how to fill in blanks with the value above?

That was cool! I changed the name of the column back to RoomArea and the script automatically updated. And now I can see the syntax for the special characters.

Names Default To Here( 1 );
dt = Current Data Table();
For( I = 2, I <= N Rows( dt ), I++,
If( :Name( "Room/Area" )[I] == "",
:Name( "Room/Area" )[I] = :Name( "Room/Area" )[I - 1]
)
);
5 REPLIES
Highlighted
stan_koprowski

Community Manager

Joined:

Aug 8, 2012

Solution

Re: Import data from Excel from SAP, how to fill in blanks with the value above?

Hi @BSwid,

Highlight the values and right-click and select Fill.

(view in My Videos)

 

 

txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: Import data from Excel from SAP, how to fill in blanks with the value above?

This little script should do what you want

names default to here(1);
dt = current data table();
for(I=2, I<=N rows(dt),  I++,
     if(:room[I] == "", :room[I] = :room[I-1]);
);
Jim
BSwid

Occasional Contributor

Joined:

Sep 25, 2018

Re: Import data from Excel from SAP, how to fill in blanks with the value above?

Before posting the question I tried creating a script using "for each row(If())" similar to your script. I didn't have success. Is there any reason for each row() shouldn't work and your for (i=) structure is better?
BSwid

Occasional Contributor

Joined:

Sep 25, 2018

Re: Import data from Excel from SAP, how to fill in blanks with the value above?

I tried your script and had the same problem. I suspected the column name as the problem. My actual column name is "Room/Area" (I modified your script to replace room with Room/Area). When I change the column name it to Room the script works fine. There must be some special character syntax for column names in JSL?
BSwid

Occasional Contributor

Joined:

Sep 25, 2018

Solution

Re: Import data from Excel from SAP, how to fill in blanks with the value above?

That was cool! I changed the name of the column back to RoomArea and the script automatically updated. And now I can see the syntax for the special characters.

Names Default To Here( 1 );
dt = Current Data Table();
For( I = 2, I <= N Rows( dt ), I++,
If( :Name( "Room/Area" )[I] == "",
:Name( "Room/Area" )[I] = :Name( "Room/Area" )[I - 1]
)
);