Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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 ...
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}>}
Both variables are loaded from the same sql database. How would I accomplish this with a flag?
Are they variables or fields?
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.
In your load script have you tried this:
If([Assignee Name] = [Customer Name], 1, 0) as Flag
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
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
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 ...
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.