Upload and compare Excel data against existing data warehouse QVD data
We have a long-standing Qlik implementation in which we load a concatenated fact table comprised of all of the necessary fact tables from our data warehouse, as well as the many associated dimensions into QVDs.
We have a new requirement from a group in the organization to be able to upload Excel sheets into Qlik to compare their hypothetical metrics, such as revenue, against existing data warehouse data as we have reported in Qlik, across various dimensions. This worked fine with a small proof of concept with a single measure and single date dimension in a sample spreadsheet, but has proven problematic when introducing an additional dimension in the spreadsheet, which will be representative of reality where the users are going to have multiple dimensions. Another challenge is that the users' spreadsheets include friendly text values for dimension descriptions/names such as '2019 Q1' or 'Dallas', whereas the fact table contains a key value for those descriptions/names such as 1 or 2. These end users are not aware of the particular key values of a given dimension.
When trying to load the data, I have not been able to get the desired results in a simple table in Qlik. If I try to concatenate the spreadsheet to the QVDs, then the problem of dimension keys vs descriptions/names prevents a correct concatenation and does not correctly assign the measure to the correct dimension. When trying to associate both the spreadsheet and the fact to the dimensions, I am getting a circular reference when loading this data and additionally Qlik is not properly aggregating the data across dimensions. I thought this might be due to columns with the 'same' data being named differently, but I am prevented from renaming the columns to have the same name.
How do I accomplish facilitating my users to be able to upload a spreadsheet with measures and dimensional descriptions/names (not key values) such that the data can be concatenated to the existing fact table, avoid circular references and provide accurate aggregation? Any help in navigating this would be very much appreciated!
