cancel
Showing results for 
Search instead for 
Did you mean: 

Incorta formula

Incortaconsulta
Cosmonaut
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)
7 REPLIES 7

JoeM
Community Manager
Community Manager

@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
Cosmonaut

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
Employee
Employee

@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? 

Screen Shot 2022-05-17 at 1.43.29 PM.png

which then shows the following:

Screen Shot 2022-05-17 at 1.43.34 PM.png

 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
Cosmonaut

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-%';