Two tables with one shared dimension and one independent dimension

[kwdaniels]

I have a simple dataset (TestDates.xlsx) and app (TestDates.qvf) with two tables: Table1 and Table2. See screenshot TestDatesNoFilter.PNG showing the two tables and filters with no filters applied.

All column names are unique across the two tables except for a shared dimension column called SharedDimension (with colors Red, Blue, Green). I want users to be able to select a value from the SharedDimension and have it apply to both tables.

By contrast, if the user filters on any of the other dimension values (e.g., Period1 in Table1 or Period2 in Table2) I do NOT want the selection in one table to affect the displayed rows in the other table. For example, if the user selects 2024 from Period1 in Table1, I do NOT want any filtering to happen on Table2. Furthermore, I do not want any values in the Period2 filter pane to be grayed out when the user filters on a value in Period1. In sum, I want Period1 and Period2 to be COMPLETELY independent if the user has not applied any filters other than Period1 and Period2.

Currently, if I filter Period1 in Table1 to 2024, it causes Table2 to show only the rows that have a SharedDimension value of Blue, since Blue is the only SharedDimension value associated with Period1 value 2024 in Table1. It similarly greys out most of the values in the Period2 filter pane, which I do not want to happen.

I realize I can use set analysis to ignore selections in a measure to achieve independence, but I would prefer not to have to do this, because 1) I have a lot of measures in the the real-world version of Table2 that I would have to litter with set analysis to ignore Period1, and 2) I don't know how to prevent legitimate values from being grayed out of the Period2 filter pane.

See screenshot TestDates2024Period1FilterApplied.png, where I select 2024 in the Period1 filter. I did not choose Blue from the SharedDimension filter, yet it indirectly imposed a Blue filter on Table2. Again, I want Period1 and Period2 to be entirely independent, but this arrangement forces a dependency between the two. I do want the ability to select Blue from the SharedDimension filter and have both tables respond accordingly, but if I don't make a SharedDimension selection, I don't want selections on Period1 or Period2 to affect each other.

Is there a way to accomplish what I'm seeking without littering all my measures with set analysis to ignore Period1 or Period2, and can I ensure that no values are grayed out in the Period2 filter pane when I select a value from Period1?

[Vegar · Accepted solution]

I have not tested this , but this might work.

Concatenate the two tables into one. Letting the Period2 and value2 be star (*) when loading table one, and similar let Period1 and Value1 be stat (*) when loading table 2.

Then activate the

Star is "*";

See https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularSt... for details on star.

Then when you select any value in Period1 then all row of table 2 will be included, but when you select color then you will filter both data sets as expected.

[kwdaniels]

Thanks, Vegar--that worked! I was not previously familiar with the "Star is" statement as a way to represent all possible values in a single column. After using this statement, the data load editor preview shows the keyword "Total" in all the unpopulated Period1 cells for the Period1 column. This is what did the trick.

I didn't have to use the * for the missing Value columns (since they're intended as measures), just for the Period columns (since they're the dimensions being filtered on).

Also, I had to deselect the "Include zero values" checkbox in Add-ons to prevent extra rows from being displayed in the tables. 

Here's the modified load script that worked for me:

Star is "*";

ConcatenatedTables:
//[Table1]:
LOAD
	[Period1],
    '*' as Period2,
	[Value1],
	[SharedDimension]
 FROM [lib://Data:DataFiles/TestDates.xlsx]
(ooxml, embedded labels, table is Table1);

//[Table2]:
Concatenate LOAD
    '*' as Period1,
	[Period2],
	[SharedDimension],
	[Value2]
 FROM [lib://Data:DataFiles/TestDates.xlsx]
(ooxml, embedded labels, table is Table2);

[Vegar]

Glad it worked out for you.  Thanks for sharing your final solution.