Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mapratt82
Creator
Creator

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
Creator
Creator
Author

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
Specialist III
Specialist III

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.

mapratt82
Creator
Creator
Author

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
Creator
Creator
Author

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.

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)