Selecting items in a chart that don't have linked records in a many to many table
Hi All,
Say I load a table with employees, a table with departments, and a many-to many table that links employees to departments. In the data source, the many to many table only contains employeeID's and a departmentID's. The departments table contains departmentID's and department names. Probably not important for my question, but the department name is added in the load script with an applymap function into the many-to-many table. Now, there are employees working in several departments, but also employees that don't work in any department. If I create a chart in a sheet with employees and departments, these employees show up in the chart with a NULL value, that is, a gray field with a hyphen (-). These 'null' department cannot be selected by clicking in the chart. What I want instead is that employees without department show up as, for instance, 'None' or 'Empty' in the chart. Using a default value in the applymap function doesn't work, because an employee without departments does not exist in the many-to-may table. Also I would like to be able to select these employees by clicking in the chart on the 'null' departments. My approach now is to handle this in the data source (a REST api), by adding employees with departmentID=0 for employees without a department. I don't like this approach because I have to generate lots of data records for items that sort of don't exist (my data model has many more dimensions with this problem). I would prefer a chart function or a script function. Our product is Qlik Sense SAAS.
NULL in tables and/or missing key-values between tables should be replaced/populated within the data-model (of course only if there a special requirements to make them accessible or displayable because they are on itself not an ERROR). Trying to do it within the UI means to do logically exactly the same as within the script - but with much more efforts and lot of disadvantages in regard to the performance and usability. And it's not purely related to Qlik else a general rule - logic + data-validation (including cleaning, replacing and populating data) belonged to the data-model and the UI is simply to show the data.
Therefore it seems that you are on the right track whereby the check which data exists or are missing could be done against the in Qlik loaded data and not against the rawdata-sources. Very useful in this regard are where-clauses with (NOT) exists(Key) and to add an extra field which contained the information if it are native records or populated ones.
NULL in tables and/or missing key-values between tables should be replaced/populated within the data-model (of course only if there a special requirements to make them accessible or displayable because they are on itself not an ERROR). Trying to do it within the UI means to do logically exactly the same as within the script - but with much more efforts and lot of disadvantages in regard to the performance and usability. And it's not purely related to Qlik else a general rule - logic + data-validation (including cleaning, replacing and populating data) belonged to the data-model and the UI is simply to show the data.
Therefore it seems that you are on the right track whereby the check which data exists or are missing could be done against the in Qlik loaded data and not against the rawdata-sources. Very useful in this regard are where-clauses with (NOT) exists(Key) and to add an extra field which contained the information if it are native records or populated ones.
Thanks, good advice. I'll use the exists function while loading the employees table to see if there is data in the many-to-many table, and store that in an extra field.
