Hi Qlik Community,
First off, I will buy a beer for whoever can help me with this issue. I've been using Qlik for about a year and just recently started working on the development side so bear with me for the question.
I'm building an application with Qlik Sense that uses data from 3 separate data sources (all QVDS), named "performance", "HC" and "Mawesome". Each data source has fiscal year and fiscal quarter fields (Fields names and data structure are different in all 3). The HC QVD is being used solely to map the other two together by empl ID and sales geo. Each data source contains many fields with the following in common(see inserted picture for more detailed view):
- "performance" contains emp id, fiscal year & quarter
- "HC" contain emp id, fiscal year, quarter & sales geo
- "Mawesome" contains fiscal year, quarter and sales geo
What is the best way to map these data sources together? The goal is to connect the data through fiscal year and fiscal quarter so that we can use one fiscal year and fiscal quarter dynamically throughout the app. We also want to be able to use sales geo dynamically throughout the app and specifically mapped to the performance data through emp ID.
I'm currently working in the data load editor and have tried creating keys, mapping tables and a number of other things that caused issues. I landed on an intermediate solution where I've renamed fiscal year and fiscal quarter fields and changed the field structure in each data source so they match. I then created a mapping load/applymap as follows to link emp id to sales geo:
mapempidtosalesgeo:
mapping LOAD EmplID, "Sub Region"
FROM [HC.qvd]
&
Performance:
load * ,
ApplyMAP('mapempidtosalesgeo',"EmplID", null()) as "Sales Geo"
resident Performance1;
drop table Performance1;
This method worked for a while with some workarounds but it created 7 synthetic keys (cringe, I know) and things are starting to break and get weird with any small change (bottom line the mapping isn't working anymore).
Any feedback or suggestions are greatly appreciated and let me know if there's additional information needed to answer/clarify.
Thank you my Qlik people
