Imbedding a For Loop in a Match Column Formula?

amurphy — Sat, 10 Jun 2023 23:32:06 GMT

Hello, I am writing a script to automate my table manipulations (primarily adding new columns with match formulas) for data with a variable number of unique column contents and final outputs.

I wrote a script to identify the unique contents in :SampleID (saved in Init_IDs) and prompted the user to rename them in a pop-up window that saves in New_IDs, I can then create my new column with a standard match formula:

New Column( "Sample",
		Character,
		Nominal,
		Formula(
			Match( :SampleID,
				"C1", "Constant 1",
				"C2", "Constant 2",
				Init_IDs[1], New_IDs[1],
				Init_IDs[2], New_IDs[2],
				Init_IDs[3], New_IDs[3],
				Init_IDs[4], New_IDs[4],
				Init_IDs[5], New_IDs[5],
				Init_IDs[6], New_IDs[6],
			)
		)
	);

This formula works for my test data set where the size of New_IDs was 6 but this will change so I'd like to use a For Loop to make it flexible, this is what I've tried but it doesn't work:

New Column( "Sample",
		Character,
		Nominal,
		Formula(
			Match( :SampleID,
				"C1", "Constant 1",
				"C2", "Constant 2",
				) +
			For( i = 1, i <= N Items( New_IDs ), i++,
				Match( :SampleID,
					Init_IDs[i], New_IDs[i],
					)
				)
		),
	);

I've checked that N Items (New_IDs) generates the value 6. I tried the For Loop alone without the combination with the other Match formula and that also doesn't work so while the '+' in between may ultimately be a problem, the primary problem appears to be my for loop itself.

I also attempted it simplified with an If statement instead of a Match formula, no luck there either:

New Column( "Sample",
		Character,
		Nominal,
		Formula(
			For( i = 1, i <= 6, i++,
				If( :SampleID == Init_IDs[i],
					New_IDs[i]
				)
			)
		)
	);

SOLUTION - Re: Imbedding a For Loop in a Match Column Formula?

amurphy — Fri, 18 Jun 2021 14:58:12 GMT

My coworker helped me out and came up with this, it works perfectly! Still need to study why my previous attempts didn't work ...

New Column( "Sample",
		Character,
		Nominal,
		Formula(
			If(Contains(Init_IDs, :SampleID), New_IDs[Contains(Init_IDs, :SampleID)],
				Match(:SampleID,
                "C1", "Constant 1",
                "C2", "Constant 2",
				)
            )
        ),
    );

topic SOLUTION - Re: Imbedding a For Loop in a Match Column Formula? in Discussions

Imbedding a For Loop in a Match Column Formula?

SOLUTION - Re: Imbedding a For Loop in a Match Column Formula?