Choose Language Hide Translation Bar
Highlighted
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
Highlighted
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

Highlighted
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

Highlighted
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
Highlighted
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

Highlighted
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

Highlighted
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?
Highlighted
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?
Highlighted
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

Article Labels

    There are no labels assigned to this post.