Conditional Column number calculation

[#1]

For each ID group in the data table, I like to calculate:

1. The count of Part Number where RANK_DESC = 1

2. The count of Part Number where RANK_ASC = 1 

   

   I like to add  two new columns PN Count of RANK_DEC =1, PN Count of RANK_ASC=1. any formulas to apply for them? 

   any other better methods to approach? thanks very much in advanced!

[#2]

For conditional calculation, JMP provides the Col XYZ() aggregation functions.
For counting, you can use Col Number(). It counts any non-missing entry.
To convert non-matching rows to "missing", you need something like Col sum (if(Rank_ASC==1 ,1,.),:Part_Number).
If Rank is 1, the if expression returns 1, for the other rows, it will return "."  (missing) - and JMP doesn't count the row.

After a first argument for the numeric input, you can specify additional columns (and expressions) to specify byGroups.
With :Part_Number as the second argument, JMP will create separate counts for each :Part_Number.

I prefer Col Sum().
To count specific rows, you can use a comparison like  Col sum (Rank_ASC==1 , :Part_Number ).
The idea behind the expression:
For rows with rank 1, the result of  the comparison Rank_ASC==1 is 1, for the other rows the result is 0.
So, col sum will count all rows with rank=1.
Here, we use again :Part_Number as a second argument. So, counts will be separate for the different Part_Numbers.

New Table( "parts",
	Add Rows( 7 ),
	New Column( "ID",
		Character,
		Set Values( {"U30372", "W30569", "W30569", "Q40040", "P40051", "P40051", "P40051"} )
	),
	New Column( "Part_Number",
		Character,
		Set Values( {"T772", "T772", "T772", "T772", "T770", "T772", "T770"} )
	),
	New Column( "Rank_ASC", Formula( Col Rank( :Part_Number, :ID ) ) ),
	New Column( "PN_Count_Rank_ASC",
		Formula( Col Sum( :Rank_ASC == 1, :Part_Number ) ),
		Set Display Width( 156 )
	),
	New Column( "RANK_DESC",
		Formula( (Col Number( :Part_Number, :ID ) - Col Rank( :Part_Number, :ID )) + 1 )
	),
	New Column( "PN_Count_Rank_DESC", Formula( Col Sum( :RANK_DESC == 1, :Part_Number ) ) )
)  

[#3]

sorry for my unclear statement.

here is my expectation:  for example, In "P40051" group, Part_number of Rank_ASC_1 == "T770", the count of "T770" in this group is 2, similar thing, Part_number of Rank_DESC_1 == "T772", the count of "T772" in this group is 1.  in "Q40040" group, Part_number of Rank_ASC_1 and Part_number of Rank_DESC_1 == "T770", the count of Part_number of Rank_DESC_1 or the count of "T770" in this group is 1. here is my expected result in right 2 columns. 

attached the data file for check. 

[#4]

Ah, right, I mixed  up the
For each ID group

with
For eachPart_Numbergroup

[#5]

This code should work:

New Table( "parts",
	Add Rows( 7 ),
	New Column( "ID",
		Character,
		Set Values( {"U30372", "W30569", "W30569", "Q40040", "P40051", "P40051", "P40051"} )
	),
	New Column( "Part_Number",
		Character,
		Set Values( {"T772", "T772", "T772", "T772", "T770", "T772", "T770"} )
	),
	New Column( "Rank_ASC", Formula( Col Rank( :Part_Number, :ID ) ) ),
	New Column( "PN_Count_Rank_ASC=1",
		Formula( Col Sum( Col Rank(:Part_Number, :ID,<< tie(minimum)) ==1,  :ID ) ),
	),

With the << tie(minimum) option, every row with the Rank 1 Part_Number will get Rank=1 - such that all such rows get counted. For this case, we can skip the intermediate step of calculating the Rank.

[#6]

Without the <<tie option, additional rows with the same ID / Part_Number will get incremental Ranks.

Here you can use a Col min( ) to replicate the Rank=1 information in all the rows of the same ID/Part_number:

• get the minimum rank of the combination :Part_Number / :ID :
  Col Minimum( :RANK_ASC,:Part_Number, :ID)
• if this min rank is 1, mark the row:
  Col Minimum( ... ) == 1
  for these rows, the result is 1, all the other rows get 0
• for every ID, count the 1s:
  Col sum( ... , :ID )

With this approach count_Rank_ASC and count_Rank_DESC can be constructed with a similar code:

New Table( "parts",
	Add Rows( 7 ),
	New Column( "ID",
		Character,
		Set Values( {"U30372", "W30569", "W30569", "Q40040", "P40051", "P40051", "P40051"} )
	),
	New Column( "Part_Number",
		Character,
		Set Values( {"T772", "T772", "T772", "T772", "T770", "T772", "T770"} )
	),
	New Column( "Rank_ASC", Formula( Col Rank( :Part_Number, :ID ) ) ),
	
	New Column( "PN_Count_Rank_ASC=1",
		Formula( Col sum(Col Minimum( :RANK_ASC,:Part_Number, :ID) ==1,  :ID ))
	),
	New Column( "RANK_DESC",
		Formula( (Col Number( :Part_Number, :ID ) - Col Rank( :Part_Number, :ID )) + 1 )
	),
	
	New Column( "PN_Count_Rank_DESC=1",
		Formula( Col sum(Col Minimum( :RANK_DESC,:Part_Number, :ID) ==1,  :ID ))
	)	
)  

[#7]

in my table, "RANK_ASC" column have been over there, I am not sure if it's defined by col Rank(:Part_Number, :ID). but I like to base on the value of RANK_ASC (RANK_ASC==1) to find the specific Part_Number, then expect the appropriate formular to calculate the count of Part_number in this group.  in your formular, I have no idea how << tie(minimum) to correlate to RANK_ASC (RAN_ASC==1). by the way, what's the formula for  RANK_DESC==1?

[#8]

For RANK_DESC=1, I am sure that one can set up a complicated formula with << tie.

Much easier: use the second approach:

https://community.jmp.com/t5/Discussions/Conditional-Column-number-calculation/m-p/870429/highlight/... 

If you don't like/trust the << tie() option, use the second approach.

if Rank_ASC and RANK_DESC are already there in the table, this is a good argument to use the second approach. No need to calculate the columns on your own.

[#9 · Accepted solution]

Here are is one option (there are plenty of other options too)

Col Sum(:Part_Number == :Part_Number[Col Min(If(:RANK_ASC == 1, Row(), .), :ID)], :ID) //change :RANK_ASC to RANK_DESC for other formula

This type of formula which uses Col Min/Max, If() and Row() can be quite useful.

Do note that it might break in any future version of JMP as these functions are not meant to be used this way. In hopes to prevent JMP from making an massive mistake I have created this wish list item Make using formula expressions in Set Each Value and using expressions as first argument in Col stat... 

[#10]

Thanks for your support, it works