If union of two non-mutually exclusive conditions (A, B, and A & B), then change a dimension’s value.

[ttmaroney]

Gurus, I need your help.

End Goal: I want to change the [Ticket Type] (or create a new Ticket Type) from whatever it currently is to “New Work” only if any of two non-mutually exclusive conditions exist:

1. The group the ticket is assigned to [Group] = ‘New Work Team’ (n=3198),
2. The ticket is a child ticket to a ticket in the ‘New Work Team’, so [Child] = 1 (n=5140),

There are n=92 of these where both conditions are true (intersection), and all of these variables are loaded on other tabs, so the only variable I want with the same name is [Ticket Number], as that is the link between the tables. I wish the IF statement had the choices of “AND,” “OR,” and “UNION,” but that’s not the case.

I’m pulling the following fields from a SQL database and an Excel workbook:

From the SQL database:

[Ticket Number]

[Ticket Type] either Incident, Request or Problem

[Group]

From an Excel workbook:

[Ticket Number]

[Child] 1 for yes, there are no 0’s

I have tried two ways to accomplish this, and both have failed, they are below:

Failed method 1:

//Identify New Work Tickets based on the group to which they assigned
NewWorkBasedOnGroup:
Load
      [Ticket Number],
      Group as zGroup,
      [Ticket Type] as [zTicket Type],
      IF([Group] = New Work Team' or Child = 1, 'New Work', [Ticket Type]) as [New Ticket Type]
RESIDENT Tickets;

//Identify New Work Tickets based on being a child ticket to a New Work Ticket
Join
NewWorkBasedOnChildTicket:
LOAD
      [Ticket #] as [Ticket Number],
      Child as NewWorkChildTickets
FROM
Data\NW_Child.xlsx
(ooxml, embedded labels, table is NW_Child);

Failed method 1:

//Identify New Work Tickets based on the group to which they assigned
NewWorkBasedOnGroup:
Load
      [Ticket Number],
      Group as zGroup,
      [Ticket Type] as [zTicket Type],
      IF([Group] = 'New Wok Team, '1', '0') as NewWorkTickets,
      IF((NewWorkTickets + NewWorkChildTickets) >0,'New Work',[Ticket Type]) as [NEW Ticket Type]
RESIDENT Tickets;

//Identify New Work Tickets based on being a child ticket to a New Work Ticket
Join
NewWorkBasedOnChildTicket:
LOAD
      [Ticket #] as [Ticket Number],
      Child as NewWorkChildTickets
FROM
Data\NW_Child.xlsx
(ooxml, embedded labels, table is NW_Child);

[swuehl]

Maybe something like

Childs:

LOAD
      [Ticket #]
FROM
Data\NW_Child.xlsx
(ooxml, embedded labels, table is NW_Child)

WHERE Child=1;

//Identify New Work Tickets based on the group to which they assigned
RESULT:
Load
      [Ticket Number],

      [Ticket Number] as TicketCheck,
      Group as zGroup,
      [Ticket Type] as [zTicket Type],
      'New Work' as [New Ticket Type]
RESIDENT Tickets

WHERE [Group]= 'New Work Team';

Concatenate (RESULT)

Load
      [Ticket Number],

      [Ticket Number] as TicketCheck,
      Group as zGroup,
      [Ticket Type] as [zTicket Type],
      'New Work' as [New Ticket Type]
RESIDENT Tickets

WHERE Exists([Ticket #],[Ticket Number]) and Not Exists([TicketCheck],[Ticket Number]);

Concatenate (RESULT)

Load
      [Ticket Number],

      [Ticket Number] as TicketCheck,
      Group as zGroup,
      [Ticket Type] as [zTicket Type],
      [Ticket Type] as [New Ticket Type]
RESIDENT Tickets

WHERE Not Exists([TicketCheck],[Ticket Number]);

DROP FIELD TicketCheck;

edit: changed second WHERE clause

[swuehl · Accepted solution]

Or maybe using a table that contains your New Work tickets (UNION first):

CHECK:

LOAD
      [Ticket #]
FROM
Data\NW_Child.xlsx
(ooxml, embedded labels, table is NW_Child)

WHERE Child=1;

Concatenate (CHECK)

Load

      [Ticket Number] as [Ticket #],

RESIDENT Tickets

WHERE [Group]= 'New Work Team' and not exists([Ticket #], [Ticket Number]);

RESULT:

Load
      [Ticket Number],

      Group as zGroup,
      [Ticket Type] as [zTicket Type],
      if(exists([Ticket #],[Ticket Number]),'New Work',[Ticket Type]) as [New Ticket Type]
RESIDENT Tickets;

[ttmaroney]

I see why you have legend status. When I used that script, added the field for [New Ticket Type], and selected only  "New Work,' it identified the correct 8246 tickets as new work, which is the first time I've seen that number. Thank you for your help.

I thought three was a problem, but is was related to missing data in the Excel workbook, once corrected all good!