topic Match and replace in QlikView

Match and replace

srinivasa1 — Fri, 14 Oct 2016 04:33:32 GMT

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.

Re: Match and replace

tamilarasu — Fri, 14 Oct 2016 06:03:01 GMT

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:

Re: Match and replace

srinivasa1 — Fri, 14 Oct 2016 17:20:19 GMT

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

Re: Match and replace

srinivasa1 — Fri, 14 Oct 2016 17:52:26 GMT

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