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]

Use Subfield to create a link table btween your tickets and owner:

LOAD TicketID,

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

RESIDENT YourTicketTable;

[Digvijay_Singh]

If you use subfield([Problem Owner],',') in the script, it may create new record for second name, try if that can be used over here with context to your script.

[Anonymous]

I have already added subfield to it. But the problem is the multiple if statements. I need to count number of tickets for Joseph and Enid. It gives a correct match for Joseph as this is the first if statement but reduces the count for Enid.

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

[Anonymous]

My issue is how to get rid of the multiple if statements. I have already used subfield to break it out. but the if statement once satisfied for the first user reduces the count for the next user. Is there an alternative to using If statement??

[swuehl]

Where have you applied the subfield() to?

Please post your complete LOAD script or a small sample QVW.

[Aurelien_Martinez]

Hi,

You can use :

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

but if you have both Joseph and Enid in the same [Problem Owner], Joseph stay your owner...

[Aurelien_Martinez]

Peraps :

Owners:

LOAD [Problem Owner],

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

RESIDENT YourTicketTable;

[Anonymous]

Hi

I have applied it to Problem Owner Field. this is my script

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,

       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

<>>>

[Anonymous]

I am counting the number of tickets as per Owner