Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

christianeatman
New Contributor

Qliksense unmatched associations causing rows to be excluded.

I have one dataset with a list of employees and information on their function, location, etc. I have another dataset with a list of recognition awards employees have received that also contains function, location, etc. for each employee award. I need to associate the two datasets on function and location so that I can filter by each and see what % of employees in a particular function have received an award.

The problem is there are functions in each dataset that are not contained in the other, so Qliksense is excluding those rows from my dashboard data. I need to retain all of the recognition award rows to calculate other KPIs such as total # of awards when no filter is selected. Is there a way to keep my associations but not lose any rows of my data?

Tags (2)
1 Solution

Accepted Solutions
rachel_delany
Contributor

Re: Qliksense unmatched associations causing rows to be excluded.

I think the best option would be to use a linking table.

In the linking table concatenate all distinct function and location combinations from each of the tables. Use a combination of the function and location as a key to connect to the other tables. You should then be able to use the function field in this table to filter the other 2 tables.

2 Replies
rachel_delany
Contributor

Re: Qliksense unmatched associations causing rows to be excluded.

I think the best option would be to use a linking table.

In the linking table concatenate all distinct function and location combinations from each of the tables. Use a combination of the function and location as a key to connect to the other tables. You should then be able to use the function field in this table to filter the other 2 tables.

crusader_
Valued Contributor

Re: Qliksense unmatched associations causing rows to be excluded.

Hi,

How are you linking those tables? compound key?

Agree with rachel.delany‌ the best approach to use link table. (As soon as data sets are completely different)

Create a compound key Autogenerate(function&location) as Key in both tables, then

[Link Table]:

LOAD DISTINCT Key, function, location Resident table1;

LOAD DISTINCT Key, function, location Resident table2;

Don't forget to remove original fields from table1 and table2.

DROP FIELDS function, location FROM table1, table2;

And then use function and location field from your [Link Table] as a dimension - which will cover both data sets.

Hope this helps.

Andrei