topic Re: is there an easy way of creating count variable in Discussions

is there an easy way of creating count variable

billi — Tue, 28 Jan 2020 23:25:45 GMT

I want to create new count variable using variables ID and use_yn (data attached). If variable use_yn for specific ID it represent subject used the service (eg. subject 3 used service twice because it has value 1 twice). Can this be done without JSL?

Re: is there an easy way of creating count variable

ron_horne — Tue, 28 Jan 2020 23:32:51 GMT

Hi @billi ,

have a look at this discussion. it has a few options
https://community.jmp.com/t5/Discussions/Syntax-How-do-I-create-an-index-column-by-ID/m-p/9751/highlight/true#M9537

let us know if it works

ron

Re: is there an easy way of creating count variable

billi — Wed, 29 Jan 2020 01:19:25 GMT

@ron_horne Thanks for quick reply. I used this formula Sum( :ID[Index( 1, Row() )] == :ID ) but I did not get the results. Data table is attached where I used formula.

Re: is there an easy way of creating count variable

Dan_Obermiller — Wed, 29 Jan 2020 16:34:42 GMT

There are several useful techniques in the link that Ron provided, but you would need to do some modifications.

Here is a column formula (stored in a column called "New Count") that I created that does the count. Note that the formula calls itself.

If(
	Row() == 1, :use_yn[1],
	:ID[Row()] == :ID[Row() - 1], :New Count[Row() - 1] + :use_yn[Row()],
	:ID[Row()] != :ID[Row() - 1], :use_yn[Row()],
	0
)

I have enclosed the data table, too. Note that if you have a missing value in "use_yn", the "New Count" will go missing. This makes sense because if you do not know if someone "used yn", then you won't know the count either.

Re: is there an easy way of creating count variable

txnelson — Wed, 29 Jan 2020 03:29:10 GMT

The Summary Platform gets you what you want. Then some simple manipulations and an update and you have your answer.

Tables==>Summary

Select both ID and use_yn as your grouping variables.

Make sure to unselect "Link to Original Data Table: and then click on OK.

The new data table has all combinations of ID and use_yn, and you only want where use_yn = 1, so, click on one cell that has use_yn with the value of 1. Then right click and select

Select Matching Cells

All rows with use_yn are selected.....but what you want are the opposite selection, so you can delete them.

Move the cursor to one of the selected rows, and then into the RowState column for that row. Right click and select Invert selection. Now just go to one of the newly selected rows, and right click and select Delete Rows. You now have the counts that you requested.

To move the data back into the original data table, simply select the original table and go to

Tables==>Update

In the dialog window, select your summary table as the one to update with.

Select Match Columns, and select both ID and use_yn as matching cells, and click on OK. The N Rows column is your count

Re: is there an easy way of creating count variable

ron_horne — Wed, 29 Jan 2020 10:30:39 GMT

Hi @billi

I have embedded both solutions from the link in your data table attached.

the first uses the colum IDIndex with the following formula: If( :ID != Lag( :ID, 1 ) | Row() == 1, 1, Lag(:IDIndex, 1 ) + 1
)

and the final column IDCount with the formula: Col Maximum(:IDIndex, :ID)

the second option will also hold in case the data is not correctly sorted. it uses the column IDIndex2 with the formula: Sum( :ID[Index( 1, Row() )] == :ID )

and the column IDCount2 with the formula: Col Maximum(:IDIndex2, :ID)

please let us know what works best for you.

ron

Re: is there an easy way of creating count variable

billi — Wed, 29 Jan 2020 16:21:32 GMT

@txnelson Thank you very much for reply. This was very easy and worked for me. Appreciate your help.

Re: is there an easy way of creating count variable

Byron_JMP — Wed, 29 Jan 2020 20:15:53 GMT

Just a quick vote for a very simple formula.

Col Number( :ID, :ID, :use_yn )

This column formula returns the number of times :ID and :use_yn are the same.