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 - thanks for the output value breakdown! Would you be able to give us an understanding of what the input values would look like? Perhaps you could attach a sample CSV?

[Incortaconsulta]

I m not able to upload .csv, it says format not supported. 

Segment1

R-50G-711

R-50G-711

R-50G-711

R-50G-711

R-50G-711

R-9KC-003

R-9KC-004

R-9KC-OC-WW

R-9KJ

R-9KJ-OC-WW

R-B6W-001-Z

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

R1607C-001

CL-VTAP-SENSOR

IXN-IPV6SR

RP7931A-80001

RP7932A-80001

RP7933A-80001

RP7935A-80001

LTPC1-CORE

RP7961A-60002

R-50G-711

R-50G-711

R-50G-711

R-50G-711

R-50G-711

[LayeredDelay]

@Incortaconsulta I am not sure if I fully understand what you expect the output to be, but are you trying to breakdown the code into separate fields? I built the first example using the Business ID using substr and if statements. Code shown below. Is this what you are looking for? Hopefully this shows how you could build out the rest of the sections? 

which then shows the following:

 If I am on the wrong track, let me know and I can take another shot at coming up with a solution, or maybe someone else has a creative way to handle it. As a last resort I might just jump into a MV and create a PySpark script to use regex to solve the challenge. 

[Incortaconsulta]

Hi - I m trying to achieve this. I did it in sql.

select case when regxp_like()segment1, '^R-[A-Z][5,6][A-Z,0-9]-[0-9][0-9][0-9]-[A-Z]$')
then 'Y'
else 'N' end as FLAG
from
(select 'R-B6W-001-Z' as SEGMENT1) a
where segment1 like 'R-%';