Expressions when the same field name is in multiple tables

[Paulp127]

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

[marmentrout5]

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]

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

Thanks again 

[marmentrout5]

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 · Accepted solution]

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