topic Re: Problem with multiple ifs + count statement in QlikView

Problem with multiple ifs + count statement

Anonymous — Thu, 09 Jun 2016 11:48:06 GMT

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.

Re: Problem with multiple ifs + count statement

swuehl — Thu, 09 Jun 2016 11:50:55 GMT

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

LOAD TicketID,

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

RESIDENT YourTicketTable;

Re: Problem with multiple ifs + count statement

Digvijay_Singh — Thu, 09 Jun 2016 11:53:47 GMT

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.

Re: Problem with multiple ifs + count statement

Anonymous — Thu, 09 Jun 2016 11:57:48 GMT

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

Re: Problem with multiple ifs + count statement

Anonymous — Thu, 09 Jun 2016 12:00:20 GMT

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

Re: Problem with multiple ifs + count statement

swuehl — Thu, 09 Jun 2016 12:06:34 GMT

Where have you applied the subfield() to?

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

Re: Problem with multiple ifs + count statement

Aurelien_Martinez — Thu, 09 Jun 2016 13:22:20 GMT

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

Re: Problem with multiple ifs + count statement

Aurelien_Martinez — Thu, 09 Jun 2016 13:24:55 GMT

Peraps :

Owners:

LOAD [Problem Owner],

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

RESIDENT YourTicketTable;

Re: Problem with multiple ifs + count statement

Anonymous — Fri, 10 Jun 2016 10:14:43 GMT

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

<>>>

Re: Problem with multiple ifs + count statement

Anonymous — Fri, 10 Jun 2016 10:15:29 GMT

I am counting the number of tickets as per Owner

Re: Problem with multiple ifs + count statement

swuehl — Fri, 10 Jun 2016 10:20:38 GMT

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;

Re: Problem with multiple ifs + count statement

Anonymous — Fri, 10 Jun 2016 10:44:40 GMT

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.

Re: Problem with multiple ifs + count statement

Anonymous — Fri, 10 Jun 2016 10:48:54 GMT

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

Re: Problem with multiple ifs + count statement

swuehl — Fri, 10 Jun 2016 10:59:19 GMT

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.

Re: Problem with multiple ifs + count statement

Anonymous — Wed, 15 Jun 2016 10:25:33 GMT

Thanks. This helped and it worked