Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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.
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.
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];
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.
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.