topic Re: how to select only those samples that meet two or more criteria in Discussions

how to select only those samples that meet two or more criteria

dhagman — Fri, 09 Jun 2023 15:58:52 GMT

i want to create a new column using Day1_Orig values wherein there are both Day1_Orig and Day1_Repeat values for the same SUBJID. So for instance, lines 113 and 114 have same SUBJID, so the new column would record the value of the Day1_Orig value in line 113 (834), but as there is no corresponding Day1_Repeat value for line 107 (641), this Day1_Orig value would not be included in the new column.

Re: how to select only those samples that meet two or more criteria

txnelson — Thu, 22 Sep 2022 21:31:00 GMT

Here is how I would handle the issue

x = .;
If( :SUBJID != Lag( :SUBJID ),
	If( Col Number( :SUBJID, :SUBJID, :VISIT ) == 1,
		x = :TiterGeoMean;
		:Day 1 Repeat = .;
	,
		:Day 1 Repeat = :TiterGeoMean;
		x = .;
	)
);
x;

Re: how to select only those samples that meet two or more criteria

jthi — Thu, 22 Sep 2022 17:28:23 GMT

Something like this might work (change column names):

If(Col Number(:O, :S) != 0 & Col Number(:R, :S),
	If(Col Cumulative Sum(!Is Missing(:O), :S) == 1,
		:O
	)
)

Names Default To Here(1);

dt = New Table("Untitled 3",
	Add Rows(6),
	New Column("S", Numeric, "Continuous", Format("Best", 12), Set Values([89, 89, 96, 96, 96, 96])),
	New Column("O", Numeric, "Continuous", Format("Best", 12), Set Values([641, ., 834, ., ., .])),
	New Column("R", Numeric, "Continuous", Format("Best", 12), Set Values([., ., ., 230, ., .]))
);

dt << New Column("Test", Numeric, Continuous, Formula(
	If(Col Number(:O, :S) != 0 & Col Number(:R, :S), /*check if both :o and :R have value for this :S*/
		If(Col Cumulative Sum(!IsMissing(:O), :S) == 1, /* Check if row is first found value for :o for this :S*/
			:O
		)
	);
));

Re: how to select only those samples that meet two or more criteria

dhagman — Fri, 23 Sep 2022 17:28:12 GMT

Thank you, this works perfectly as long my dataset is sorted/organized such that the retest values always follow the originals.

Re: how to select only those samples that meet two or more criteria

dhagman — Fri, 23 Sep 2022 17:33:56 GMT

Jim, thanks for your suggestion, but it remains beyond my novice level of understanding and ability to execute.

Re: how to select only those samples that meet two or more criteria

jthi — Fri, 23 Sep 2022 17:41:07 GMT

Seems to work fine for my simple test data but I might be missing something