Match and replace

[srinivasa1]

Hi Please guide how I can achieve below based on above sample data

"1.How I can only extract Inbound link key (ABC003) when I have type Bug and it has referenced key in ReportID (ABC-003)-SourceError.

If type =Bug and any of UrlLink extratced key(ABC-003 or ABC-05)

matched with ReportID key  then I need to Replace Url link with Macthed Report ID or Point

Example: If my urlKey(ABC-003 macthed with ReportID-ABC-003 then my my point Will be reaplced with 5 insted of 10"

Please find attached sample example on same.

Thanks in advance for any guidance.

[tamilarasu]

Hi Srinivasa D,

Have a look at the attachment.

Map:
Mapping LOAD ReportID, 
     '$' as Point
FROM
BugReport.xlsx
(ooxml, embedded labels, table is Sheet1) Where Not IsNull(ReportID);

Data:
LOAD ReportID, 
     Type, 
     UrlLink, 
     Point, 
     If(Type='Bug',If(Substringcount(Mapsubstring('Map',UrlLink),'$')>0,5,Point),Point) as [New Point]
FROM
BugReport.xlsx
(ooxml, embedded labels, table is Sheet1) Where Not IsNull(ReportID);

Result:

[srinivasa1]

Thanks Nagaraj   for helpful information                  ,

I need to it should be dynamic and  I would like to replace Urllink and point

Example : Report ID row (ABC-004) urllink should be replaced with 'ABC-005' and Point should be 3 when it matched with ReportID(ABC-005).

THx

[srinivasa1 · Accepted solution]

Thanks Naga,

I made some changes to script looks like it is working now. See below my changes to code.

If(Type='Bug',If(Substringcount(Mapsubstring('Map',UrlLink),'$')>0,SubField(UrlLink,':',-1) ,Point),Point) as [New Point]

Thx