9 Replies Latest reply: Mar 10, 2016 9:56 AM by Tom Maroney RSS

    Filter data when two dimensions match

    Tom Maroney

      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?

        • Re: Filter data when two dimensions match
          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}>}

          • Re: Filter data when two dimensions match
            Tom Maroney

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

              • Re: Filter data when two dimensions match
                Sunny Talwar

                Are they variables or fields?

                  • Re: Filter data when two dimensions match
                    Tom Maroney

                    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.

                      • Re: Filter data when two dimensions match
                        Sunny Talwar

                        In your load script have you tried this:

                         

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

                        • Re: Filter data when two dimensions match
                          Puttemans Johan

                          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

                            • Re: Filter data when two dimensions match
                              Tom Maroney

                              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 

                                • Re: Filter data when two dimensions match
                                  Sunny Talwar

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

                                    • Re: Filter data when two dimensions match
                                      Tom Maroney

                                      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.