05-13-2022 02:00 AM
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)
05-19-2022 11:54 AM
@Ram -
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
)
05-19-2022 12:44 PM
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"
)