870410_en-US

liliconcom

Conditional Column number calculation

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&nbsp;</CODE></STRONG></P> <SPAN class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="ReliabilityWolf_0-1745749897393.png" style="width: 400px;"><SPAN class="lia-inline-image-display-wrapper" image-alt="ReliabilityWolf_0-1745749897393.png" style="width: 400px;"><SPAN class="lia-inline-image-display-wrapper" image-alt="ReliabilityWolf_0-1745749897393.png" style="width: 400px;"><SPAN class="lia-inline-image-display-wrapper" image-alt="ReliabilityWolf_0-1745749897393.png" style="width: 400px;"><SPAN class="lia-inline-image-display-wrapper" image-alt="ReliabilityWolf_0-1745749897393.png" style="width: 400px;"><SPAN class="lia-inline-image-display-wrapper" image-alt="ReliabilityWolf_0-1745749897393.png" style="width: 400px;"><SPAN class="lia-inline-image-display-wrapper" image-alt="ReliabilityWolf_0-1745749897393.png" style="width: 400px;"><SPAN class="lia-inline-image-display-wrapper" image-alt="ReliabilityWolf_0-1745749897393.png" style="width: 400px;"><SPAN class="lia-inline-image-display-wrapper" image-alt="ReliabilityWolf_0-1745749897393.png" style="width: 400px;"><SPAN class="lia-inline-image-display-wrapper" image-alt="ReliabilityWolf_0-1745749897393.png" style="width: 400px;"><SPAN class="lia-inline-image-display-wrapper" image-alt="ReliabilityWolf_0-1745749897393.png" style="width: 400px;"><SPAN class="lia-inline-image-display-wrapper" image-alt="ReliabilityWolf_0-1745749897393.png" style="width: 400px;"><SPAN class="lia-inline-image-display-wrapper" image-alt="ReliabilityWolf_0-1745749897393.png" style="width: 400px;"><span class="lia-inline-image-display-wrapper" image-alt="ReliabilityWolf_0-1745749897393.png" style="width: 400px;"><img src="https://us.v-cdn.net/6038925/uploads/images/75219iB37346FFBB9C212B/ReliabilityWolf_0-1745749897393.png" role="button" title="ReliabilityWolf_0-1745749897393.png" alt="ReliabilityWolf_0-1745749897393.png" /></span></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN> <P>I like to add&nbsp;<SPAN>&nbsp;two new columns PN Count of RANK_DEC =1,&nbsp;PN Count of RANK_ASC=1. any formulas to apply for them?&nbsp;</SPAN></P> <SPAN>any other better methods to approach? thanks very much in advanced!</SPAN></LI> </OL></LINGO-BODY><LINGO-LABS id="lingo-labs-870410" slang="en-US" mode="CREATE"><LINGO-LABEL>Basic Data Analysis and Modeling</LINGO-LABEL></LINGO-LABS><LINGO-SUB id="lingo-sub-870421" slang="en-US" mode="UPDATE">Re: Conditional Column number calculation</LINGO-SUB><LINGO-BODY id="lingo-body-870421" slang="en-US" mode="UPDATE"><P>For conditional calculation, JMP provides the <FONT face="courier new,courier">Col XYZ()</FONT> aggregation functions.<BR />For counting, you can use&nbsp;<FONT face="courier new,courier">Col Number(). </FONT>It counts any non-missing entry.<FONT face="courier new,courier"><BR /></FONT>To convert non-matching rows to "missing", you need something like&nbsp;<FONT face="courier new,courier">Col sum (if(Rank_ASC==1 ,1,.),:Part_Number).<BR /></FONT>If Rank is 1, the <FONT face="courier new,courier">if</FONT> expression returns 1, for the other rows, it will return "."&nbsp; (missing) - and JMP doesn't count the row.<BR /><BR />After a first argument for the numeric input, you can specify additional columns (and expressions) to specify byGroups.<BR />With <FONT face="courier new,courier">:Part_Number</FONT> as the second argument, JMP will create separate counts for each :Part_Number.<BR /><BR />I prefer <FONT face="courier new,courier">Col Sum().<BR /></FONT>To count specific rows, you can use a comparison like&nbsp;&nbsp;<FONT face="courier new,courier">Col sum (Rank_ASC==1 , :Part_Number ).<BR /></FONT>The idea behind the expression: <BR />For rows with&nbsp;<FONT face="courier new,courier">rank 1, </FONT>the result of&nbsp; the comparison&nbsp;<FONT face="courier new,courier">Rank_ASC==1 </FONT>is 1, for the other rows the result is 0.<BR />So, <FONT face="courier new,courier">col sum</FONT> will count all rows with rank=1.<BR />Here, we use again&nbsp;<FONT face="courier new,courier">:Part_Number</FONT> as a second argument. So, counts will be separate for the different Part_Numbers.<BR /><BR /></P> <PRE><CODE class=" language-jsl">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 ) ) ) )    

Re: Conditional Column number calculation

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. 

Re: Conditional Column number calculation

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

 

with
For eachPart_Numbergroup

Re: Conditional Column number calculation

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.

Re: Conditional Column number calculation

Without the < 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 )</CODE></LI> </UL> <P><SPAN>With this approach <FONT face="courier new,courier">count_Rank_ASC</FONT> and <FONT face="courier new,courier">count_Rank_DESC</FONT> can be constructed with a similar code:</SPAN></P> <PRE><CODE class=" language-jsl">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 )) ) )

   

Re: Conditional Column number calculation

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?

Re: Conditional Column number calculation

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 statistical formulas supported behaviour 

Re: Conditional Column number calculation

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/true#M103370 

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.

Re: Conditional Column number calculationThanks for your support, it worksRe: Conditional Column number calculation

welcome.