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

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

BSwid — Thu, 25 Oct 2018 17:01:39 GMT

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?

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

stan_koprowski — Thu, 25 Oct 2018 17:19:32 GMT

Hi @BSwid,

Highlight the values and right-click and select Fill.

(view in My Videos)

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

txnelson — Thu, 25 Oct 2018 17:36:28 GMT

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

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

BSwid — Thu, 25 Oct 2018 18:21:57 GMT

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?

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

BSwid — Thu, 25 Oct 2018 18:31:54 GMT

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?

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

BSwid — Thu, 25 Oct 2018 18:35:58 GMT

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