Creating (composite?) Key in Load script

[Amberj_29]

Hi All,

I need a key to link two files by three dimensions. I found the below script somewhere else and loads fine, however, I'm having an issue with filtering by the common key, it only shows dimensions in charts and maps from table 1 and not table 2. For example, if I have 'Country_C' as a filter and filter by England, only table 1 will display on map and in tables, and data from Table 2 will be removed. 

Any help on where I am going wrong on this would be great:

------------------------------

LOAD
Isbn,
Title,
"Product-Group",
"Doc Date" as "Sales Date",
Quantity,
"Gross Value",
"Net Val",
"Cust Name",
"Add 1",
"Add 2",
"Add 3",
"Customer_Type",
"Postcode Out",
"Postcode In",
latitude,
longitude,
Country as "Country1",
District as "District1",
autonumber("Doc Date"&'-'&"Country"&'-'&"District") as Key,
'Table1' as Type
FROM [lib://Internal - Other Clients:DataFiles/Sales Data.xlsx]
(ooxml, embedded labels, table is [Table1]);

LOAD
"Test Centre",
"Month" as "Theory Date",
"Score Male",
"Score Female",
"% Score",
"Postcode area",
"TT_PC Out",
"Full PC",
"TT_District" as "District2",
"TT_Country" as "Country2",
"TT_latitude",
"TT_longitude",
"Who had the Highest Pass rate",
"Who had the Lowest Pass rate",
autonumber("Month"&'-'&"TT_District"&'-'&"TT_Country") as Key,
'Table2' as Type
FROM [lib://Internal - Other Clients:DataFiles/tests.xlsx]
(ooxml, embedded labels, table is [Table2]);

Common_temp:

load

"Sales Date" as "Month_C",

"Country1" as "Country_C",

"District1" as "District_C",

Type as Common_Type

resident [Table 1];

concatenate

Load

"Theory Date" as "Month_C",

"Country2" as "Country_C",

"District2" as "District_C",

Type as Common_Type

resident [Table 2];

Common:

LOAD *,

autonumber("Month_C"&'-'&"Country_C"&'-'&"District_C") as Key

Resident Common_temp;

drop table Common_temp;

drop field Type;

[tm_burgers · Accepted solution]

Table2 looks like the order of your AutoNumber() function is different; the others go Month-Country-District; and Table2 goes ...  autonumber("Month"&'-'&"TT_District"&'-'&"TT_Country") 

It should work if you put it in the correct order.

[Amberj_29]

Thanks, I didn't think putting it in the same order would make the difference, but it did! Many thanks,