Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
mapratt82
Contributor

Dimension Search Problem

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)

1 Solution

Accepted Solutions
mapratt82
Contributor

Re: Dimension Search Problem

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.

View solution in original post

4 Replies
JustinDallas
Valued Contributor II

Re: Dimension Search Problem

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.

Highlighted
mapratt82
Contributor

Re: Dimension Search Problem

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];

mapratt82
Contributor

Re: Dimension Search Problem

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.

View solution in original post

Re: Dimension Search Problem

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.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)