2 Replies Latest reply: Oct 25, 2018 11:47 PM by Anil Samineni RSS

    Dimension Search Problem

    Mark Pratt

      I have a customer name field that I am filtering out employees from using an if statement. The filtering appears to be working fine, however when I click on the magnifying glass to search for a particular customer the results pane goes blank. I take out the if statement and it works fine. I verified that the name is in the list after the if statement. I also tried using the if statement to create a master item same outcome. I've never noticed this before. Everything but the search function seems to be working correctly. Please help.

       

      Here are the if statements I've tried:

      if(WildMatch(Employee, 'No'), CustomerName)

      if(Employee = 'No', CustomerName)

      if(Employee <> 'Yes', CustomerName)

       

      set analysis:

      aggr(only({$<Employee = {'No'} >}CustomerName), CustomerName)

        • Re: Dimension Search Problem
          Justin Dallas

          Can you make a demo dataset for us?  There are many ways you can have a relationship between Customers and Employees and that might be what's going on.

            • Re: Dimension Search Problem
              Mark Pratt

              I can't really do a dataset, perhaps this will help as this is the only place anything employee is being pulled in. In addition I tried removing the if statement and just using the Employee field as a filter and it worked perfectly. Unfortunately I need this filtered on the backend as employee account info is not to be seen. Thanks!

               

              LIB CONNECT TO DataSource1;


              Nav2:


              Load

                  Text(Account_Number) &'_'& Text(Account_Type) as AccountNum,

                  If(EOD_Code > 0, 1,0) as EOD;

              SQL SELECT Account_Number,

                  Account_Type,

              EOD_Code

              From Table1;


              LIB CONNECT TO DataSource2;


              JOIN (Nav2)


              Load

                  Text(AccountNumber) &'_'& Text(AccountType) as AccountNum,

                  HouseholdCode;

              SQL Select

                  AC.dimAccountID,

                  AC.dimCustomerID,

                  ACCT.dimAccountID,

                  ACCT.AccountNumber,

                  ACCT.AccountType,

                  CUST.CustomerID,

                  CUST.HouseholdCode,

                  CUST.HouseholdCustomerType,

                  CUST.HouseholdDisplayName

              From

              TableA as AC,

              TableB as ACCT,

              TableC as CUST

              Where

              AC.dimAccountID = ACCT.dimAccountID

              and

              AC.dimCustomerID = CUST.CustomerID;


              EOD:

              Load

              HouseholdCode,

              if(Max(EOD)=1, 'Yes', 'No') as Employee

              Resident [Nav2]   

              Group by [HouseholdCode];

              Drop Table [Nav2];

                • Re: Dimension Search Problem
                  Anil Samineni

                  LIB CONNECT TO DataSource2;


                  JOIN (Nav2)


                  Load

                      Text(AccountNumber) &'_'& Text(AccountType) as AccountNum,

                      HouseholdCode;

                  Load

                       ACCT.AccountNumber as AccountNumber,

                       ACCT.AccountType as AccountType,

                       CUST.HouseholdCode as HouseholdCode;

                  SQL Select

                      AC.dimAccountID,

                      AC.dimCustomerID,

                      ACCT.dimAccountID,

                      ACCT.AccountNumber,

                      ACCT.AccountType,

                      CUST.CustomerID,

                      CUST.HouseholdCode,

                      CUST.HouseholdCustomerType,

                      CUST.HouseholdDisplayName

                  From

                  TableA as AC,

                  TableB as ACCT,

                  TableC as CUST

                  Where

                  AC.dimAccountID = ACCT.dimAccountID

                  and

                  AC.dimCustomerID = CUST.CustomerID;

                   

                  Here where i feel wrong? Can you cross check with Connection 2 and comment 1st data source. Will see, What data it returns. Because, Initially you need to add like above as Highlighted. Then Associate and DM looks good.

              • Re: Dimension Search Problem
                Mark Pratt

                The problem was somewhere in the linkage. Instead of having a separate EOD table I used a join to make it part of the table with the HouseholdCode. It now works as it should. I guess sometimes you're better off doing you're own joining rather than letting Qlik do it. Of course I believe our version is from mid 2016, before all the awesome upgrades so that could be playing a part.