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;
