Filter data when two dimensions match

[ttmaroney]

I have dimensions called Assignee and Customer, and I want to filter the data to see only work tickets which are self-initiated. My thought is to create a dimension called Self Initiated and have the value set to 1 (or true) if Assignee=Customer and 0 or false if it does not. I want to see the work tickets that are self initiated. What is the best way to accomplish this?

[sunny_talwar]

If Assignee and Customer comes from the same table, you can surely create a flag (0 or 1) and then use the flag in your set analysis {<Flag = {1}>}

[ttmaroney]

Both variables are loaded from the same sql database. How would I accomplish this with a flag?

[sunny_talwar]

Are they variables or fields?

[ttmaroney]

Both are Fields [Assignee Name] and [Customer Name], and both fields load and I can filter by them. For now I made a table with those fields and [Ticket Number], exported it to Excel, and made a variable [Self Gen] and made it 1 if the [Assignee Name] =[Customer Name] and 0 if it doesn't, then loaded that spreadsheet into QlikView with only the [Ticket Number] and [Self Gen] fields. The problem is we have over 550,000 tickets and now I've added a manual process that's will not update when I reload data. There has to be a better way.

[sunny_talwar]

In your load script have you tried this:

If([Assignee Name] = [Customer Name], 1, 0) as Flag

[puttemans]

You can stay in Qlik, add the variable as Sunny defined. I'd give it a twist, but the formula remains :

IF([Assignee Name] = [Customer Name], 'yes', 'no') as [Self Initiated].

If you make a table in Qlik with the 2 fields you used to load from Excel ([Ticket Number] and [Self Gen] fields), and you make sure you can select 'yes' in the Self Initiated field, then I think you get what you need.

Regards,

Johan

[ttmaroney]

With both methods I am getting a script error when I reload the data after adding the code.  I have added a tab in the script editor at demoted that tab to the last. With both methods, the coma after [Customer Name] is underlined in red (I underlined it below). That makes me think there is a simple syntax error, like a missing {} or sa space issue.

IF([Assignee Name] = [Customer Name], 'yes', 'no') as [Self Initiated]

If([Assignee Name])=([Customer Name]),1,0) as Flag 

[sunny_talwar · Accepted solution]

Are you adding this within your load statement, correct?

LOAD [Assignee Name],

          [Customer Name],

          OtherFields,

          If([Assignee Name] = [Customer Name], 1, 0) as Flag

FROM/Resident ...

[ttmaroney]

WOW!. Both of you guys have been very helpful. There is no way I could have done with without your help.  I am building an application upon a load statement written by someone else that pulls data directly from an SQL database.  This is part of the first tab of the load:

SELECT Customer.last_name + ', ' + Customer.first_name AS [Customer Name]
              ,Assignee.last_name + ', ' + Assignee.first_name AS [Assignee Name]

...

So I took Sunny's input on the load statement (which made the red underline in the condition go away), and I used Putteman's if statement:

SelfGen:
Load
      [Assignee Name],
      [Customer Name],
IF([Assignee Name] = [Customer Name], 'yes', 'no') as [Self Initiated]

RESIDENT Tickets
;

And it works like a champ with no additional labor, which is the way I like it.  Thank you both again.