Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Paulp127
Partner
Partner

Expressions when the same field name is in multiple tables

I am having difficulty understanding the interaction of table associations and selections/expressions, when the same field appears in multiple tables. 

In the diagram there is a simple database schema. There is location data for customers and for retail stores.

Suppose I want to see the locations associated with the customers who bought a certain item. How would I do this in an expression, say to create a dimension for a table?

if (item_id = 10, location_id) would be ambiguous - would the location_id refer to the value in the retail stores table or the customers table.

 

Thanks

Labels (1)
1 Solution

Accepted Solutions
Paulp127
Partner
Partner
Author

Hi again.

After further investigation, I discovered that the data structure in the example involves circular references which are   bad in Qlik. The solution is to have two separate location tables, one for customers and one for stores.

 

Thanks for you help

View solution in original post

4 Replies
marmentrout5
Contributor
Contributor

Depends on how it was modeled.  If location_id is in multiple tables, then each one will have to be referenced in an expression. 

The better solution is to review the model, possibly create a location dimension table, storing all locations there and then there is only one location_id to reference for any expressions.

Paulp127
Partner
Partner
Author

Hi Marmentrout5. How would I reference each location_id in the expression?

Thanks again 

marmentrout5
Contributor
Contributor

As an example:

<table1>.location_id

<table2>.location_id

Count(<table1>.location_id)

Count(<table2>.location_id)

Per your original example:

if (item_id = 10, <table1>.location_id)

Paulp127
Partner
Partner
Author

Hi again.

After further investigation, I discovered that the data structure in the example involves circular references which are   bad in Qlik. The solution is to have two separate location tables, one for customers and one for stores.

 

Thanks for you help

View solution in original post