Incorta formula

[Incortaconsulta]

Spoiler

can this formula be achieved in Incorta?

I have a segment column with multiple Inputs, but I want the output to be displayed in the report only if it met the below criteria. 

Sample output : 

R-B6W-001-Z

Description Standard as per Core Software Monetization Project is as below for all R Number (R-x5/6y-xxx-x)

• R-x = x is character only --- represent business ID (CSG, NAS, Prisma…etc)
• R-x5/6 = fixed value --- represent the license type (perpetual or subscription)
• R-x6y = Y can be alpha/numeric --- y represent RMU
• R-x6y-xxx = xxx is numeric only --- represent license term (Floating, node-locked, transportable, USB)
• R-x6y-xxx-x = x is alpha only --- represent duration (6 months / 12/36/…etc)

[JoeM]

@Incortaconsulta -  

It might be good to use a materialized view if you want to do some flexible RegEx pattern matching. If you are trying to accomplish this in the formula, I've written up a formula that can evaluate the match based on character position. Note that this will only work with fixed-length strings. If the patten lengths need to be variable, then this will not work. 

case(
	and(
		substring(
			Problem.CommunitySample.Segment1, 
			0, 
			1
		) = 'R', 
		isAlpha(
			substring(
				Problem.CommunitySample.Segment1, 
				2, 
				1
			)
		), 
		or(
			int(
				substring(
					Problem.CommunitySample.Segment1, 
					3, 
					1
				)
			) = 5, 
			int(
				substring(
					Problem.CommunitySample.Segment1, 
					3, 
					1
				)
			) = 6
		), 
		or(
			isAlpha(
				substring(
					Problem.CommunitySample.Segment1, 
					4, 
					1
				)
			), 
			isNumeric(
				substring(
					Problem.CommunitySample.Segment1, 
					4, 
					1
				)
			)
		), 
		int(
			substring(
				Problem.CommunitySample.Segment1, 
				6, 
				3
			)
		) < 1000, 
		isAlpha(
			substring(
				Problem.CommunitySample.Segment1, 
				10, 
				1
			)
		)
	), 
	1, 
	0
)

[dylanwan]

We can use splitPart to first tokenize the segments.  Here is the code that might work for you.

if(
	splitPart(
		MVTest.formula_test.segment, 
		'-', 
		1
	) = 'R', 
	if(
		length(
			splitPart(
				MVTest.formula_test.segment, 
				'-', 
				2
			)
		) = 3, 
		if(
			length(
				splitPart(
					MVTest.formula_test.segment, 
					'-', 
					3
				)
			) = 3, 
			if(
				length(
					splitPart(
						MVTest.formula_test.segment, 
						'-', 
						4
					)
				) = 1, 
				if(
					isAlpha(
						substring(
							splitPart(
								MVTest.formula_test.segment, 
								'-', 
								2
							), 
							0, 
							1
						)
					), 
					if(
						in(
							substring(
								splitPart(
									MVTest.formula_test.segment, 
									'-', 
									2
								), 
								1, 
								1
							), 
							"5", 
							"6"
						), 
						if(
							isNumeric(
								splitPart(
									MVTest.formula_test.segment, 
									'-', 
									3
								)
							), 
							if(
								isAlpha(
									splitPart(
										MVTest.formula_test.segment, 
										'-', 
										4
									)
								), 
								"Y", 
								"N"
							), 
							"N"
						), 
						"N"
					), 
					"N"
				), 
				"N"
			), 
			"N"
		), 
		"N"
	), 
	"N"
)