BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
BSwid
Contributor

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

0 Kudos
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 in My Videos)

 

 

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
Contributor

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]
)
);
0 Kudos
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.

(view in My Videos)

 

 

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
Contributor

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?
0 Kudos
BSwid
Contributor

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?
0 Kudos
BSwid
Contributor

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