Discussion Board for collaboration related to QlikView App Development.
I have two tables; Data and Entities
The Entities table is used to filter the Data table. I need to create a relationship between the following:
CountryID - EntityID
ContinentID - EntityID
StateID - EntityID
Is there anyway to link multiple fields in one table to a single field in another table?
Your assistance is greatly appreciated.
Go to Solution.
You will first need to transform the top table into a tall thin version. CrossTable() will do this for you. Try:
The two tables will then link on EntityID.
Hope this helps,
Thanks for the reply.
I currently do not have a field called 'EntityType'. Am I renaming my fields my EntityType?
I'm not 100% sure how cross tables work, hence my question.
You can read up on CrossTable() in the reference manual. Essentially, it transforms the data from many columns to 2 columns of name-value pairs. So, in your case, your data will look like this:
You probably don't need your Entities and IDs except for the lookup. So you can do that lookup / mapping in the load, and keeping only the necessary data, like
Mapping LOAD EntityID,
(html, codepage is 1252, embedded labels, table is @2)
applymap('CountryMap',CountryID,'unknown') as CountryName,
applymap('ContinentMap',ContinentID,'unknown') as ContinentName,
applymap('StateMap',StateID,'unknown') as StateName
(html, codepage is 1252, embedded labels, table is @1);
edit: Missed a map on upload
The Cross Table worked perfectly.
Thanks a million