nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

JMP help in script

HSS

Hello,
I am trying to update a column based on other columns. If update is not possible, I am happy to create a new column as well using formula. But I am not able to mage to do that. See my sample and solution data and images.
So lets say, for a
"customer_id" = A, if "Site_B" is missing and "Data_set_ID" = 221 or 169 or 136 => Put "Site_B" = "Site_B" at "Data_set_ID" = 142

And
Another similar condition (upto 4 or 5 conditions)
And repeat the same for all customers -- B, C, AA, AD etc.
Any help please?
Thanks.

Sample_Data_Solution .jpg

Sample_Data.jmp

Sample_Data_Solution.jmp

Accepted answers

txnelson

This should give you a leg up on solving what you need

names default to here(1);
dt=current data table();

summarize( dt, cusID = By(:Customer_ID) );

For( i = 1, i <= N Rows( dt ), i++,
	currCustomer_ID = :Customer_ID[i];
	For( k = 1, k <= N Items( cusID ), k++, 
		If( :Customer_ID[i] == cusID[k],
			If(
				Is Missing( :Site_B[i] ) & (:Data_set_ID[i] == 44 | :Data_set_ID[i] == 87),
					:Site_B[i] = :Site_B[(dt << get rows where( :Data_set_ID == 142 & :Customer_ID == currCustomer_ID ))[1]],
				Is Missing( :Site_B[i] ) & (:Data_set_ID[i] == 221 | :Data_set_ID[i] == 169 | :Data_set_ID[i] == 136),
					:Site_B[i] = :Site_B[(dt << get rows where( :Data_set_ID == 164 & :Customer_ID == currCustomer_ID ))[1]]
			)
		)
	);
);

All comments

txnelson

From the syntax used in your Discussion request, you are not educated in the syntax of JSL. You need to take the time to read the Scripting Guide to become proficient in JSL.

Below is the JSL required to meet what I understand as what you need, based upon the Blue and Red pseudo code in the Sample_Data_Solution.jpg

names default to here(1);
dt=current data table();

for(i=1,i<=N Rows(dt),i++,
	currCustomer_ID = :Customer_ID[i];
	If(:Customer_ID[i] == "A",
		if(IsMissing(:Site_B[i]) & (:Data_set_ID[i] == 44 | :Data_set_ID[i] == 87),
				:Site_B[i] = :Site_B[ (dt << get rows where( :Data_set_ID == 142 & :Customer_ID == currCustomer_ID))[1]]
		,
			IsMissing(:Site_B[i]) & (:Data_set_ID[i] == 221 | :Data_set_ID[i] == 169 | :Data_set_ID[i] == 136),
				:Site_B[i] = :Site_B[ (dt << get rows where( :Data_set_ID == 164 & :Customer_ID == currCustomer_ID))[1]]
		)
	,
	:Customer_ID[i] == "B",
		if(IsMissing(:Site_B[i]) & (:Data_set_ID[i] == 44 | :Data_set_ID[i] == 87),
				:Site_B[i] = :Site_B[ (dt << get rows where( :Data_set_ID == 142 & :Customer_ID == currCustomer_ID))[1]]
		,
			IsMissing(:Site_B[i]) & (:Data_set_ID[i] == 221 | :Data_set_ID[i] == 169 | :Data_set_ID[i] == 136),
				:Site_B[i] = :Site_B[ (dt << get rows where( :Data_set_ID == 164 & :Customer_ID == currCustomer_ID))[1]]
		)
	)
);

HSS

Hi Nelson,
Thanks for reply and comment. I need to learn lot of things in JMP.
I am looking for a nested "For Loop". I mean in your script I need another loop on "Customer_ID". In my data set I have more than 6000-7000 customer IDs.
Thanks.

HSS

Hi,
I mean in your code I can not assign customer_id manually like, in side the if statement --

:Customer_ID[i] == "A",
:Customer_ID[i] == "B",
This would be 6000-7000 alpha numeric variable for me. Like
:Customer_ID[i] == "9UHIW",
:Customer_ID[i] == "7EIGO", etc.
so I need another loop which identify each unique Customer_ID and then implement your script on it.
Thanks.

txnelson

This should give you a leg up on solving what you need

names default to here(1);
dt=current data table();

summarize( dt, cusID = By(:Customer_ID) );

For( i = 1, i <= N Rows( dt ), i++,
	currCustomer_ID = :Customer_ID[i];
	For( k = 1, k <= N Items( cusID ), k++, 
		If( :Customer_ID[i] == cusID[k],
			If(
				Is Missing( :Site_B[i] ) & (:Data_set_ID[i] == 44 | :Data_set_ID[i] == 87),
					:Site_B[i] = :Site_B[(dt << get rows where( :Data_set_ID == 142 & :Customer_ID == currCustomer_ID ))[1]],
				Is Missing( :Site_B[i] ) & (:Data_set_ID[i] == 221 | :Data_set_ID[i] == 169 | :Data_set_ID[i] == 136),
					:Site_B[i] = :Site_B[(dt << get rows where( :Data_set_ID == 164 & :Customer_ID == currCustomer_ID ))[1]]
			)
		)
	);
);