63962_en-US

liliconcom

Add data from previous columns if missing

Hi,

 

I´m trying to create a column with the latest available date in my data set. So in this case, if a date is missing form visit 19, I get the the date from visit 18. That I can do. But if the visit 18 date is missing I can´t make it work anymore. So how do I make jmp start in one column and go back until there is a date?

 

Br Julie

Re: Add data from previous columns if missing

You just discard the If( isMissing(  clause completely if you want.  It is not really changing anything, as long as the Date Last Seen column has all missing values in it

Re: Add data from previous columns if missing

Ok - that makes sense. But running the script doesn´t add any values to the new column.

 

Shouldn´t the "Is Missing" statement point to column 19? Or am I missing something?

Re: Add data from previous columns if missing

My error on the i=5.  I was testing with it....I have changed it to i=1.

If you want a new column, then you can just add it as your 20th column, and use the second script.

Names Default To Here( 1 );
dt = Current Data Table();

dt << New Column("Last Date Seen",format("yyyy-mm-dd",12));

For( i = 1, i <= N Rows( dt ), i++,
 If( Is Missing( As Column( "Last Date Seen" )[i] ),
  Column( "Last Date Seen" )[i] = Max( dt[i, 0] )
 )
);

Re: Add data from previous columns if missing

But won´t that just fill in the last column with the latest date instead of creating a new column?

 

Also, in the first ecample, why is it "i = 5"?

 

Thanks in advance

Re: Add data from previous columns if missing

Here are 2 ways of doing this

Names Default To Here( 1 );
dt = Current Data Table();

For( i = 1, i <= N Cols( dt ), i++,
 missingRows = dt << get rows where( Is Missing( As Column( i ) ) );
 If( N Rows( missingRows ) > 0,
  For( k = 1, k <= N Rows( missingRows ), k++,
   Column( i )[missingRows[k]] = Column( i - 1 )[missingRows[k]]
  )
 );
);

Names Default To Here( 1 );
dt = Current Data Table();

For( i = 1, i <= N Rows( dt ), i++,
 If( Is Missing( As Column( 19 )[i] ),
  Column( 19 )[i] = Max( dt[i, 0] )
 )
);

Re: Add data from previous columns if missing

If you start iterating from the first row, you should be able to fill in a missing date with the previous date as you iterated down the table. It seems simple enough to me but I might have missed an important point.