I have been looking at a few old boards and questions about composite keys. I have manage to load the data, however, now that I have more than two tables my associations work for only half the data. For example, in the loaded data below "Country_Unified" (and the other key dimensions/metrics only now works for data from the "[Channel and Country]" workbook. When I look at the data model viewer it looks right and should work but it doesn't. When I had two tables - [Workbook1] and [Channels and countries] the associations between country and account number worked. It is when I added the third sheet [workbook2] and added another value for the key that is started to break. I did try to see if without the third key it would work, sadly it did not. Does anyone know how to assist on making a composite key across more than two tables?
LOAD
name,
Notes,
Tier,
status,
"account no." as "Account_Number1",
Vlookup_Country as "Country_CMS",
Vlookup_website,
autonumber("account no."&'-'&Vlookup_Country) as Key
FROM [lib://Marketing and Sales - Internal:DataFiles/Workbook1.xlsx]
(ooxml, embedded labels, table is [Workbook1]);
LOAD
CC_Isbn,
CC_Title,
"CC_Cust No." as "Account_Number2",
CC_Portfolio as "Portfolio1",
CC_Country as "Country_CC",
"Country Other (search)",
"CC_Reporting Month",
latitude,
longitude,
CC_Region_Min,
"CC_Region Higher tier",
autonumber("CC_Cust No."&'-'&CC_Country&'-'&CC_Portfolio) as Key
FROM [lib://Marketing and Sales - Internal:DataFiles/Channels and Countries - for Qlik upload.xlsx]
(ooxml, embedded labels, table is [Channel and Country]);
LOAD
EI__C,
EI,
"EI Product" as "Portfolio2",
PRODUCT__C,
Exams_Country as "Country_Exams",
"Number of Exams",
Month_of_Exam,
autonumber(Exams_Country&'-'&"EI Product") as Key
FROM [lib://Marketing and Sales - Internal:DataFiles/Workbook2.xlsx]
(ooxml, embedded labels, table is [Workbook2]);
Common_temp:
load
"Account_Number1" as "C_Account Number",
"Country_CMS" as "Country_Unified"
resident [Workbook1];
concatenate
Load
"Account_Number2" as "C_Account Number",
"Country_CC" as "Country_Unified",
"Portfolio1" as "Portfolio"
resident [Channel and Country];
concatenate
Load
"Country_Exams" as "Country_Unified",
"Portfolio2" as "Portfolio"
resident [Workbook2];
Common:
LOAD *,
autonumber("C_Account Number"&'-'&Country_Unified&'-'&Portfolio) as Key
Resident Common_temp;
drop table Common_temp;
I additionally want to add a fourth sheet and add another key - month as the fourth sheet will then merge by country and month to [Channel & Country] and [workbook2] and will join only by country to [workbook1]. I did manage to load it, but again I had the same issue that my keys only worked for half the data, so when I select "Country-Unified" I could not go across the Qlik App and look at all things occurring in a sad country. As I said I have been looking at old boards, if there is currently a better practice, please let me know.
