Help with Sequence formula

Sburel — Sat, 10 Jun 2023 23:25:21 GMT

Hello,

I've been working on a script which leverage the matched-pair platform. By the end of the script, I end up with a cleaned up table of significance.

In the column 'Sig Level', you have 2 type of label concatenated: time point label for the repeat measure (0, 14, 28) and the p-value labels ( Prob...). I would like to find a way to put together a formula which would produce the result in column 'Repeat Measure Time Range'. Briefly, you have a repeating sequence of 5 rows and I want to aggregate the 2nd and 1st row from each repeat sequence and have that info represented in the 'Repeat Measure Time Range' for the 5 rows from their respective sequence. Currently I have to do that manually.

Some help would be really appreciated.

Best,

Sebastien

Here's the table

New Table( "repeated measurement significance",
	Add Rows( 45 ),
	New Column( "concatenated 'by' variable",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"709547, 700 µg #", "709547, 700 µg #", "709547, 700 µg #",
			"709547, 700 µg #", "709547, 700 µg #", "709547, 700 µg #",
			"709547, 700 µg #", "709547, 700 µg #", "709547, 700 µg #",
			"709547, 700 µg #", "709547, 700 µg #", "709547, 700 µg #",
			"709547, 700 µg #", "709547, 700 µg #", "709547, 700 µg #",
			"1394216, 300 µg #", "1394216, 300 µg #", "1394216, 300 µg #",
			"1394216, 300 µg #", "1394216, 300 µg #", "1394216, 300 µg #",
			"1394216, 300 µg #", "1394216, 300 µg #", "1394216, 300 µg #",
			"1394216, 300 µg #", "1394216, 300 µg #", "1394216, 300 µg #",
			"1394216, 300 µg #", "1394216, 300 µg #", "1394216, 300 µg #",
			"PBS, 0 ug #", "PBS, 0 ug #", "PBS, 0 ug #", "PBS, 0 ug #",
			"PBS, 0 ug #", "PBS, 0 ug #", "PBS, 0 ug #", "PBS, 0 ug #",
			"PBS, 0 ug #", "PBS, 0 ug #", "PBS, 0 ug #", "PBS, 0 ug #",
			"PBS, 0 ug #", "PBS, 0 ug #", "PBS, 0 ug #"}
		)
	),
	New Column( "Repeat Measure Significance",
		Numeric,
		"Continuous",
		Format( "PValue", 12 ),
		Set Selected,
		Set Values(
			[., ., 0.056681634999684, 0.028340817499842, 0.971659182500158, ., .,
			0.0132403647849572, 0.00662018239247861, 0.993379817607521, ., .,
			0.0226394499943625, 0.0113197249971813, 0.988680275002819, ., .,
			0.077484061203898, 0.038742030601949, 0.961257969398051, ., .,
			0.0235819507950532, 0.0117909753975266, 0.988209024602473, ., .,
			0.0136375428618946, 0.00681877143094731, 0.993181228569053, ., .,
			0.436392991056214, 0.218196495528107, 0.781803504471893, ., .,
			0.00150837641570982, 0.000754188207854909, 0.999245811792145, ., .,
			0.00415746703431022, 0.00207873351715511, 0.997921266482845]
		)
	),
	New Column( "Sig Level",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"14", "0", "Prob > |t|", "Prob > t", "Prob < t", "28", "0",
			"Prob > |t|", "Prob > t", "Prob < t", "28", "14", "Prob > |t|",
			"Prob > t", "Prob < t", "14", "0", "Prob > |t|", "Prob > t", "Prob < t",
			"28", "0", "Prob > |t|", "Prob > t", "Prob < t", "28", "14",
			"Prob > |t|", "Prob > t", "Prob < t", "14", "0", "Prob > |t|",
			"Prob > t", "Prob < t", "28", "0", "Prob > |t|", "Prob > t", "Prob < t",
			"28", "14", "Prob > |t|", "Prob > t", "Prob < t"}
		)
	),
	New Column( "Repeat Measure Time Range",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"0-14", "0-14", "0-14", "0-14", "0-14", "0-28", "0-28", "0-28", "0-28",
			"0-28", "14-28", "14-28", "14-28", "14-28", "14-28", "0-14", "0-14",
			"0-14", "0-14", "0-14", "0-28", "0-28", "0-28", "0-28", "0-28", "14-28",
			"14-28", "14-28", "14-28", "14-28", "0-14", "0-14", "0-14", "0-14",
			"0-14", "0-28", "0-28", "0-28", "0-28", "0-28", "14-28", "14-28",
			"14-28", "14-28", "14-28"}
		)
	)
)

Re: Help with Sequence formula

ron_horne — Wed, 27 Jan 2021 19:56:39 GMT

Hi @Sburel

Try this as a quick fix:

 	New Column("Column 5", Character, "Nominal", Formula(If(Row() == 1, Lag(:Sig Level, -1) || "-" || :Sig Level, Lag(:Sig Level) == "Prob < t", Lag(:Sig Level, -1) || "-" || :Sig Level, Lag(:Column 5))), Set Selected);

there must be a better way of getting what you want than a formula in a column but this should do the trick.

Re: Help with Sequence formula

Sburel — Wed, 27 Jan 2021 20:53:08 GMT

Hi Ron,

thanks a lot for the quick fix. It certainly did the job. Having said that, I would be interested in hearing about the better way to do this.

Best,

Sebastien

Re: Help with Sequence formula

ron_horne — Wed, 27 Jan 2021 23:28:12 GMT

With respect to a better solution, I meant to say that these values could come from a previous or later stage of the process you are going through. Otherwise, at this point just use the formula.

Glad i could help.

topic Re: Help with Sequence formula in Discussions

Help with Sequence formula

Re: Help with Sequence formula

Re: Help with Sequence formula

Re: Help with Sequence formula