Problem with multiple ifs + count statement

[Anonymous]

Hi

I am required to count the number of tickets assigned to a particular Problem Owner.

Field Problem has 2 names separated by a delimiter ', ' so the same ticket needs to be counted for both the users example - Problem Owner has values - CA Joseph, CA Enid. Then the same ticket needs to be counted for Joseph once and then for Enid once.

Since the users often assign a lot prefixes I am using wildmatch to identify the Problem Owners

example

   if(WildMatch([Problem Owner], '*Joseph*'),'Joseph',
if(WildMatch([Problem Owner], '*Enid*'),'Enid', as Owners

I have used multiple If statements so the problem that I am facing is that when the if condition is satisfied by Joseph then it jumps out of the loop and the ticket doesnt get counted for Enid even if both Joseph and Enid worked on the ticket. Hence the actual count for the tickets resolved for Enid will be lower wherever both Joseph and Enid may have worked upon it.

Is there an alternative to writing multiple ifs for this issue.

[swuehl]

I think this code will create duplicate lines of your ticket facts per owner, but it should already split your Problem Owner field appropriately.

I've suggested to use a link table instead:

Tickets:

LOAD

     Priority,

   if(Priority like '*High*', '1.High',

     if( Priority like '1. CSS TR','IT Alerts',

     if(Priority like '*2. Medium*','2.Medium',

     if(Priority like '*2.1*','2.1 Medium - Multiple Customer Impact',

     if(Priority like '*2.2*','2.2 Medium - Critical care Customer Impact',

     if(Priority like '*2.3*','2.3 Medium - Tier 1 Customer Impact',

     if(Priority like '*2.4*','2.4 Medium - Other Impact','3.Low'))))))) as TR_Priority,

      if(App like '*HEXABC*','CONNECT','HSBCNET') as Application,

    [TR #] as Ticket_Reference,

          [Resolved Date],

          MonthName([Resolved Date]) as Month_Resolved,

    if(Priority <> '1. CSS TR' and [Resolved Date]>'', NetWorkDays([Date Raised],[Resolved Date])) as TR_BPS_Elapsed_Days,

             if(Priority = '1. CSS TR' and [Resolved Date]>'',NetWorkDays([Date Raised],[Resolved Date])) as TR_IT_Alerts_Elapsed_Days,

     [Problem Owner]

       SubField(  [Problem Owner],',')as   [Problem Owner],

      if(WildMatch([Problem Owner], '*ENID*'),'Enid',

            if(WildMatch([Problem Owner], '*Cloudy*'),'Cloudy',

       if(WildMatch([Problem Owner], '*ZHENGHU*'),'Zhenghu',

       if(WildMatch([Problem Owner], '*CHAI*'),'Andrew Chai',

if(WildMatch([Problem Owner], '*joseph*'),'Joseph'  ,

           if(WildMatch([Problem Owner], '*kapil*'),'Kapil',

)))))) as Owners,

if(WildMatch([Problem Owner],'*CA*'),'Van') as [GLT Site]

FROM

<>>>

LOAD [Ticket_Reference],

          Subfield([Problem Owner],',') as Owners

RESIDENT Tickets;

[Anonymous]

This works but there is still one issue. I need to have clean names.. There are prefixes against the names.

Example - CA Joseph, CA Andrew, CA Kapil. I need to display only Joseph, Andrew, Kapil etc

If i use purgechar it doesn't work fine.. Is there some way to fix it.

[Anonymous]

Not only prefixes but garbage suffixes in the names as well. How do i get rid of it?

[swuehl · Accepted solution]

There are different approaches.

If you know the possible names and the requested outcome, you can use a MAPPING table approach:

Data Cleansing

If you don't know all names, but you know rules to transform input to output, you can make use of various string and match functions QV scripting offers.

[Anonymous]

Thanks. This helped and it worked