Help with Sequence formula

[#1]

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"}
		)
	)
)

[#2 · Accepted solution]

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.

[#3]

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

[#4 · Accepted solution]

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.