topic Fill empty cells based on evaluating of value different column in rows above in Discussions

Fill empty cells based on evaluating of value different column in rows above

Engelke — Thu, 08 Jun 2023 16:27:18 GMT

I have the following problem:

I want to fill cells via a formula or script in Column "Parent List" absed on next n-1 value in column Level:

Fill emty cell (Column „Parent List“, Row 10):

•In row with emty cell go to Level column containing value „4“

•Calculate n-1 („3“)

•Find next row above that contains value n-1 (3), which is row No 9. Copy character (7P61G05) of Material column into emty cell (Parten List, Row 10):

Fill all emty cells accordingly.

Additionally there might occur the following sepcial case:

Numbering goes down: still select row with n-1 value in cell

i hope anyone can solve that! Thanks you in advance,

Matthias

Re: Fill empty cells based on evaluating of value different column in rows above

jthi — Mon, 20 Mar 2023 08:31:53 GMT

Could you provide data table with the data and one column which contains correct values?

Re: Fill empty cells based on evaluating of value different column in rows above

Engelke — Mon, 20 Mar 2023 10:18:07 GMT

Yes of course, thank you. In column "Desired Column" I copied the cells from "Material" column that I would expect the script should insert, depending on values in column "Level!

Re: Fill empty cells based on evaluating of value different column in rows above

Jed_Campbell — Mon, 20 Mar 2023 16:50:40 GMT

If I'm understanding correctly, this JSL should do what you're looking for:

/*JMP STATISTICAL DISCOVERY LLC (“JMP”) PERMITS THE USE OF THIS COMPUTER SOFTWARE CODE (“CODE”) ON AN AS-IS BASIS AND AUTHORIZES YOU TO USE THE CODE SUBJECT TO THE TERMS LISTED HEREIN.  BY USING THE CODE, YOU AGREE TO THESE TERMS.  YOUR USE OF THE CODE IS AT YOUR OWN RISK.  JMP MAKES NO REPRESENTATION OR WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO, WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, NONINFRIGEMENT, AND TITLE, WITH RESPECT TO THE CODE.
You may use the Code solely as part of a software product you currently have licensed from JMP, JMP’s parent company SAS Institute Inc. (“SAS”), or one of JMP’s or SAS’s subsidiaries or authorized agents (the “Software”), and not for any other purpose.  The Code is designed to add functionality to the Software but has not necessarily been tested.  Accordingly, JMP makes no representation or warranty that the Code will operate error-free.  JMP is under no obligation to maintain, support, or continue to distribute the Code.
Neither JMP nor its licensors shall be liable to you or any third-party for any general, special, direct, indirect, consequential, incidental, or other damages whatsoever arising out of or related to your use or inability to use the Code, even if JMP has been advised of the possibility of such damages.  Except as otherwise provided above, the Code is governed by the same agreement that governs the Software.  If you do not have an existing agreement with JMP or SAS governing the Software, you may not use the Code.
JMP and all other JMP Statistical Discovery LLC product or service names are registered trademarks or trademarks of JMP Statistical Discovery LLC in the USA and other countries.  ® indicates USA registration.  Other brand and product names are registered trademarks or trademarks of their respective companies.
*/
Names Default To Here( 1 );
dt = Current Data Table();

For Each Row(
	dt,
	If( Row() > 1,
		look_for_n = :Level[Row()] - 1; //find Level - 1
		rows_with_n = dt << get rows where( :Level == look_for_n ); //get all rows that match
		last_n = rows_with_n[N Items( rows_with_n )]; //get the last of those rows
		:Desired Column = Char( :Material[last_n] ); //put into desired column
	)
);

Re: Fill empty cells based on evaluating of value different column in rows above

Engelke — Tue, 21 Mar 2023 08:31:18 GMT

Hello Jed,

This is fantastic! Thank you so much for the great support! There is a small buck still, maybe you can resolve that too?

The script seems to look for the n-1 number further downwards in the Level Column while it should look for the last n-1, in this case Level „3“, that is before the cell to be filled! So it took the wrong characters in cells of row 10 to 14.I created the column „Correct“ were you can see the correct filled cell:

The script uses characters "D9844" from next upcoming Level 3 instead of "7P61G05" from last Level 3 before the respective row.

I will add the jmp table.

Thank you in advance and best regards,

Matthias

Re: Fill empty cells based on evaluating of value different column in rows above

Jed_Campbell — Tue, 21 Mar 2023 14:17:29 GMT

Ah, now I understand better, I think, what you're looking for. Here's an updated script, as well as a link to the free and excellent online JMP Scripting course

/*JMP STATISTICAL DISCOVERY LLC (“JMP”) PERMITS THE USE OF THIS COMPUTER SOFTWARE CODE (“CODE”) ON AN AS-IS BASIS AND AUTHORIZES YOU TO USE THE CODE SUBJECT TO THE TERMS LISTED HEREIN.  BY USING THE CODE, YOU AGREE TO THESE TERMS.  YOUR USE OF THE CODE IS AT YOUR OWN RISK.  JMP MAKES NO REPRESENTATION OR WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO, WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, NONINFRIGEMENT, AND TITLE, WITH RESPECT TO THE CODE.
You may use the Code solely as part of a software product you currently have licensed from JMP, JMP’s parent company SAS Institute Inc. (“SAS”), or one of JMP’s or SAS’s subsidiaries or authorized agents (the “Software”), and not for any other purpose.  The Code is designed to add functionality to the Software but has not necessarily been tested.  Accordingly, JMP makes no representation or warranty that the Code will operate error-free.  JMP is under no obligation to maintain, support, or continue to distribute the Code.
Neither JMP nor its licensors shall be liable to you or any third-party for any general, special, direct, indirect, consequential, incidental, or other damages whatsoever arising out of or related to your use or inability to use the Code, even if JMP has been advised of the possibility of such damages.  Except as otherwise provided above, the Code is governed by the same agreement that governs the Software.  If you do not have an existing agreement with JMP or SAS governing the Software, you may not use the Code.
JMP and all other JMP Statistical Discovery LLC product or service names are registered trademarks or trademarks of JMP Statistical Discovery LLC in the USA and other countries.  ® indicates USA registration.  Other brand and product names are registered trademarks or trademarks of their respective companies.
*/

Names Default To Here( 1 );
dt = Current Data Table();
row()=10;
For Each Row(
	dt,
	If( Row() > 1,
		n = row(); //temporary row variable for later
		look_for_n = :Level[Row()] - 1; //find Level - 1
		rows_with_n = dt << get rows where( :Level == look_for_n & row() < n ); //get all rows that match
		last_n = rows_with_n[N Items( rows_with_n )]; //get the last of those rows
		:Desired Column = Char( :Material[last_n] ); //put into desired column
	)
);

Re: Fill empty cells based on evaluating of value different column in rows above

Engelke — Wed, 22 Mar 2023 07:49:37 GMT

Dear Jed

Awesome, the script works perfectly, as intended! Thank you so much for the support. This will be integral part of a script that will provide SAP data in a form that makes the data accessible for material trackings, which is a pain in SAP. My previous script already made JMP quite popular within the company as it is widely used. Thank you also for the scripting course. That is definitely something that I would like to learn better.

So thanks again and best regards,

Matthias

Re: Fill empty cells based on evaluating of value different column in rows above

jthi — Wed, 22 Mar 2023 08:52:20 GMT

Below is other idea as it uses a bit different method than Jed does

Names Default To Here(1);

// format data
dt = Open("$DOWNLOADS\Fill empty cells V2.jmp");
Column(dt, "Desired Column")[10::N Rows(dt)] = ""; // set empty values
Column(dt, "Desired Column") << Set Name("Parent List");

//Column(dt, "Parent List") << Set Each Value( I would suggest creation of new column
dt << New Column("ParentListFixed", Character, Nominal, << Set Each Value(
	As Constant(
		m_levels = :Level << get values;
		m_materials = :Material << get values;
	);
	If(Is Missing(:"Parent List"n) & !Is Missing(:Level),
		level_to_search = :Level - 1;
		If(level_to_search > 0,
			row_of_interest = Max(Loc(m_levels[1::Row()], level_to_search));
			m_materials[row_of_interest];
		,
			"";
		);
	,
		:"Parent List"n
	);
));