Find matching date in row

JulieSAppel

Hi,

I´m trying to create a script (or formula) where I search each column within a row in a data table for a matching date. I want to create a column with a simple yes/no response to whether a "dispensed date" matches the date in any of the visit columns.For that purpose I created a formula with an if statement as shown below but it gets me the wrong result. E.g. the outcome in the first row should be "yes" as there is a match with the Visit 8 column. What am I doing wrong?

 

If(
 :Dispensed Date == (:SCREENING | :Name( "VISIT 2 (WEEK 0)" ) |
 :Name( "VISIT 3 (WEEK 2)" ) | :Name( "VISIT 4 (WEEK 4)" ) |
 :Name( "VISIT 5 (WEEK 8)" ) | :Name( "VISIT 6 (WEEK 10)" ) |
 :Name( "VISIT 7 (WEEK 12)" ) | :Name( "VISIT 8 (WEEK 14)" ) |
 :Name( "VISIT 9 (WEEK 16)" ) | :Name( "VISIT 10 (WEEK 20)" ) |
 :Name( "VISIT 11 (WEEK 26)" ) | :Name( "VISIT 12 (WEEK 29)" ) |
 :Name( "VISIT 13 (WEEK 32)" ) | :Name( "VISIT 14 (WEEK 35)" ) |
 :Name( "VISIT 15 (WEEK 38)" ) | :Name( "VISIT 16 (WEEK 41)" ) |
 :Name( "VISIT 17 (WEEK 45)" ) | :Name( "VISIT 18 (WEEK 52)" ) |
 :Name( "VISIT 19 (WEEK 57)" )),
 "yes",
 "no"
)

compare dates.jmp

txnelson

Here is the correct syntax for the If() statement you are writing

If(
	:Dispensed Date == :SCREENING |
	:Dispensed Date == :Name( "VISIT 2 (WEEK 0)" ) |
	:Dispensed Date == :Name( "VISIT 3 (WEEK 2)" ) |
	:Dispensed Date == :Name( "VISIT 4 (WEEK 4)" ) |
	:Dispensed Date == :Name( "VISIT 5 (WEEK 8)" ) |
	:Dispensed Date == :Name( "VISIT 6 (WEEK 10)" ) |
	:Dispensed Date == :Name( "VISIT 7 (WEEK 12)" ) |
	:Dispensed Date == :Name( "VISIT 8 (WEEK 14)" ) |
	:Dispensed Date == :Name( "VISIT 9 (WEEK 16)" ) |
	:Dispensed Date == :Name( "VISIT 10 (WEEK 20)" ) |
	:Dispensed Date == :Name( "VISIT 11 (WEEK 26)" ) |
	:Dispensed Date == :Name( "VISIT 12 (WEEK 29)" ) |
	:Dispensed Date == :Name( "VISIT 13 (WEEK 32)" ) |
	:Dispensed Date == :Name( "VISIT 14 (WEEK 35)" ) |
	:Dispensed Date == :Name( "VISIT 15 (WEEK 38)" ) |
	:Dispensed Date == :Name( "VISIT 16 (WEEK 41)" ) |
	:Dispensed Date == :Name( "VISIT 17 (WEEK 45)" ) |
	:Dispensed Date == :Name( "VISIT 18 (WEEK 52)" ) |
	:Dispensed Date == :Name( "VISIT 19 (WEEK 57)" ),
	"yes",
	"no"
);