topic Re: Counting redundant rows using by variables - different outcome for continuous and nominal by variables? in Discussions

Counting redundant rows using by variables - different outcome for continuous and nominal/character by variables?

Ressel — Sat, 10 Jun 2023 23:46:36 GMT

Here I am counting redundant rows based on info held in 2 columns (Batch ID & Value [concentration]).

Col Cumulative Sum( 1, :Batch ID, :"Value [concentration]"n )

However, based on whether Value [concentration] is formatted as continuous or character variable in my table, a different result is obtained (see screenshots below).

Outcome with Value [concentration] as continuous variable (not what I want)

Outcome with Value [concentration] as character variable (what I want)

Is there some computational issue (missing values...?) I need to wrap my head around to understand this?

Thank you!

P.s.: I realize that it may appear nonsensical to count combinations of Batch ID and missing values, but there are practical reasons for this at the time of writing.

Edit: Revised "nominal" to "character" in text.

Re: Counting redundant rows using by variables - different outcome for continuous and nominal by variables?

jthi — Thu, 31 Mar 2022 08:57:05 GMT

Some with more knowledge will have to provide correct answer why this is happening with numeric columns (your second example has Character column, not just Nominal numeric column), but you might be able to handle it depending on what you are doing by using something like:

Col Cumulative Sum( 1, :Batch ID, Col Stored Value(:"Value [concentration]"n ))

Col Cumulative Sum( 1, :Batch ID, IsMissing(:"Value [concentration]"n ))

Re: Counting redundant rows using by variables - different outcome for continuous and nominal by variables?

Ressel — Thu, 31 Mar 2022 09:12:53 GMT

Yes, sorry for using incorrect terminology. I have revised that now in the original post.

I changed the concentration data temporarily to character format, since I know (from experience) that this is working.

Thank you for your reply!

Re: Counting redundant rows using by variables - different outcome for continuous and nominal by variables?

Ressel — Fri, 01 Apr 2022 16:49:32 GMT

Thank you for the example by the way. (Downloaded & saved.) I still find it illogical and weird that the numerical column as by variable doesn't recognize the missing value.