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
BSwid
Level IV

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
stan_koprowski
Community Manager Community Manager

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 solution in original post

txnelson
Super User

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

View solution in original post

BSwid
Level IV

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]
)
);

View solution in original post

5 REPLIES 5
stan_koprowski
Community Manager Community Manager

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.

 

 

txnelson
Super User

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
Level IV

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
Level IV

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
Level IV

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]
)
);