Here is my Load script that I'm trying to get to work, but it gets to about a minute and a half and stalls for a minute and then says "operation aborted". I'm assuming it's crashing as it's trying to make more synthetic keys.
LIB CONNECT TO [SQL CONNECTION];
[invoice_statistics]:
LOAD
[STATISTIC_NO],
[INVOICE_DATE],
year([INVOICE_DATE]) as [STAT_YEAR_NO],
month([INVOICE_DATE]) as [STAT_PERIOD_NO],
[INVOICED_QTY],
[COST],
[BASE_SALE_UNIT_PRICE],
[NET_AMOUNT],
[GROSS_AMOUNT],
[CATALOG_DESC],
[CUSTOMER_NAME],
[COUNTRY_DESC],
[CUST_GRP_DESC],
[MARKET_DESC],
[REGION_DESC],
[DISTRICT_DESC],
[SALESMAN],
[ORDER_NO],
[CATALOG_GROUP_DESC],
[CUSTOMER_NO],
[CUST_GRP],
[CONTRACT],
[CATALOG_NO],
[PART_NO],
[SALESMAN_CODE],
[SALES_UNIT_MEAS],
[PRICE_UNIT_MEAS],
[CATALOG_GROUP],
[PART_DESC],
[CUSTOMER_PRICE_GRP_DESC],
[INVOICE_NO],
[SERIES_ID],
[parent_cust_id],
[Convt_UOM],
[Convt_QTY],
[convt_price],
[parent_cust_name],
[CITY],
[CATALOG_NO] & ' - ' & [CATALOG_DESC] as [SALES_PART],
APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([COUNTRY_DESC])), '-') AS [invoice_statistics.COUNTRY_DESC_GeoInfo],
APPLYMAP( '__countryCodeIsoTwo2Polygon', UPPER([SERIES_ID]), '-') AS [invoice_statistics.SERIES_ID_GeoInfo];
SELECT "STATISTIC_NO",
"INVOICE_DATE",
"INVOICE_ID",
"INVOICED_QTY",
COST,
"BASE_SALE_UNIT_PRICE",
"NET_AMOUNT",
"GROSS_AMOUNT",
"CATALOG_DESC",
"CUSTOMER_NAME",
"COUNTRY_DESC",
"CUST_GRP_DESC",
"MARKET_DESC",
"REGION_DESC",
"DISTRICT_DESC",
SALESMAN,
"ORDER_NO",
"CATALOG_GROUP_DESC",
"CUSTOMER_NO",
"CUST_GRP",
CONTRACT,
"CATALOG_NO",
"PART_NO",
"SALESMAN_CODE",
"SALES_UNIT_MEAS",
"PRICE_UNIT_MEAS",
"CATALOG_GROUP",
"PART_DESC",
"CUSTOMER_PRICE_GRP_DESC",
"INVOICE_NO",
"SERIES_ID",
"parent_cust_id",
"Convt_UOM",
"Convt_QTY",
"convt_price",
"parent_cust_name",
CITY
FROM "QLIK_V2_TestingCD".dbo."invoice_statistics";
TAG FIELD [COUNTRY_DESC] WITH '$geoname', '$relates_invoice_statistics.COUNTRY_DESC_GeoInfo';
TAG FIELD [invoice_statistics.COUNTRY_DESC_GeoInfo] WITH '$geopolygon', '$hidden', '$relates_COUNTRY_DESC';
TAG FIELD [COUNTRY_CODE] WITH '$geoname', '$relates_invoice_statistics.COUNTRY_CODE_GeoInfo';
TAG FIELD [invoice_statistics.COUNTRY_CODE_GeoInfo] WITH '$geopolygon', '$hidden', '$relates_COUNTRY_CODE';
TAG FIELD [SERIES_ID] WITH '$geoname', '$relates_invoice_statistics.SERIES_ID_GeoInfo';
TAG FIELD [invoice_statistics.SERIES_ID_GeoInfo] WITH '$geopolygon', '$hidden', '$relates_SERIES_ID';
DROP TABLES __countryAliasesBase, __countryGeoBase;
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;
DERIVE FIELDS FROM FIELDS [STATISTIC_DATE], [INVOICE_DATE], [CREATION_DATE], [LAST_USE_DATE], [PART_CREATED], [DATE_ENTERED] USING [autoCalendar] ;
LIB CONNECT TO 'SQL CONNECTION';
LOAD CONTRACT,
PART_NO,
PART_PRODUCT_CODE,
PART_PRODUCT_FAMILY,
PART_STATUS,
PLANNER_BUYER,
UNIT_MEAS,
ABC_CLASS,
CREATE_DATE,
LAST_ACTIVITY_DATE,
SUPPLY_CODE,
TYPE_CODE,
TYPE_DESIGNATION,
NEGATIVE_ON_HAND,
INVENTORY_PART_COST_LEVEL,
LIFECYCLE_STAGE,
FREQUENCY_CLASS,
DECLINE_DATE,
EXPIRED_DATE,
Catalog_Desc;
[inventory_parts]:
SELECT CONTRACT,
"PART_NO",
"PART_PRODUCT_CODE",
"PART_PRODUCT_FAMILY",
"PART_STATUS",
"PLANNER_BUYER",
"UNIT_MEAS",
"ABC_CLASS",
"CREATE_DATE",
"LAST_ACTIVITY_DATE",
"SUPPLY_CODE",
"TYPE_CODE",
"TYPE_DESIGNATION",
"NEGATIVE_ON_HAND",
"INVENTORY_PART_COST_LEVEL",
"LIFECYCLE_STAGE",
"FREQUENCY_CLASS",
"DECLINE_DATE",
"EXPIRED_DATE",
"Catalog_Desc"
FROM "QLIK_V2_TestingCD".dbo."inventory_parts";
LOAD PART_PRODUCT_FAMILY,
DESCRIPTION AS [PF_DESC],
PF_CONCAT;
[inventory_prod_fams]:
SELECT "PART_PRODUCT_FAMILY",
DESCRIPTION,
"PF_CONCAT"
FROM "QLIK_V2_TestingCD".dbo."inventory_prod_fams";
[sales_reps_basic]:
LOAD
[SALESMAN_CODE],
[NAME],
[Status],
[DIVISION_FLAG] AS [COMPONENT];
SELECT "SALESMAN_CODE",
NAME,
Status,
"DIVISION_FLAG"
FROM "QLIK_V2_TestingCD".dbo."sales_reps_basic";
[SQLDBV2_DivisionsHierarchy]:
LOAD
[DIVISION],
[DIVISION LVL2],
[DIVISION LVL3],
[DIVISION LVL4],
[DIVISION LVL5],
if([COMPONENT]='FASTPATCH / POLYQUICK', 'FASTPATCH/POLYQUICK', [COMPONENT]) as [COMPONENT]
FROM [lib://1. Raw Data:DataFiles/SQLDBV2_DivisionsHierarchy.qvd]
(qvd);
[SQLDBV2_Divisions]:
LOAD
[COMPONENT],
[DIVISION] AS [SQLDBV2_Divisions.DIVISION],
[LastReload]
FROM [lib://1. Raw Data:DataFiles/SQLDBV2_Divisions.qvd]
(qvd);
LIB CONNECT TO 'SQL CONNECTION';
LOAD id,
sec_site,
contract AS [CONTRACT],
part_type,
part_no as [PART_NO],
component_part,
description as [component_desc],
type_code,
lpp,
app,
std_cost,
lprd,
qoh,
uom,
last_consumed,
inv_value,
avg_6mth_usage,
units_on_order,
value_on_order,
component_part & ' - ' & description AS [COMPONENT_FULL];
[wvco_inventory]:
SELECT id,
"sec_site",
contract,
"part_type",
"part_no",
"component_part",
description,
"type_code",
lpp,
app,
"std_cost",
lprd,
qoh,
uom,
"last_consumed",
"inv_value",
"avg_6mth_usage",
"units_on_order",
"value_on_order"
FROM "QLIK_V2_TestingCD".dbo."wvco_inventory"
WHERE COMPONENT_PART NOT IN ('H2O','H2O-HOT', 'H2O-LB', '0', '00');
LIB CONNECT TO 'SQL CONNECTION';
LOAD CONTRACT,
STAT_YEAR_NO,
STAT_PERIOD_NO,
PART_NO as [component_part],
QUANTITY,
QTY_IN_TRANSIT,
QTY_AT_CUSTOMER,
VENDOR_OWNED_QTY,
TOTAL_VALUE,
DESCRIPTION,
STAT_YEAR_NO & ' - ' & STAT_PERIOD_NO AS [YEARMONTH];
[inv_value_per_period]:
SELECT CONTRACT,
"STAT_YEAR_NO",
"STAT_PERIOD_NO",
"PART_NO",
QUANTITY,
"QTY_IN_TRANSIT",
"QTY_AT_CUSTOMER",
"VENDOR_OWNED_QTY",
"TOTAL_VALUE",
DESCRIPTION
FROM "QLIK_V2_TestingCD".dbo."inv_value_per_period";
LIB CONNECT TO 'SQL CONNECTION';
LOAD ORDER_NO as [PO_NO],
BUYER_CODE,
CONTRACT,
PART_NO as [component_part],
ADDITIONAL_COST_AMOUNT,
ADDITIONAL_COST_INCL_TAX,
BUY_QTY_DUE,
BUY_UNIT_PRICE,
BUY_UNIT_PRICE_INCL_TAX,
DATE_ENTERED,
Year(DATE_ENTERED) as [STAT_YEAR_NO],
Month(DATE_ENTERED) AS [STAT_PERIOD_NO],
REVISED_QTY,
VENDOR_NO,
TAX_AMOUNT_BASE,
INVOICING_SUPPLIER,
STATE;
[PO_Lines]:
SELECT "ORDER_NO",
"BUYER_CODE",
CONTRACT,
"PART_NO",
"ADDITIONAL_COST_AMOUNT",
"ADDITIONAL_COST_INCL_TAX",
"BUY_QTY_DUE",
"BUY_UNIT_PRICE",
"BUY_UNIT_PRICE_INCL_TAX",
"DATE_ENTERED",
"REVISED_QTY",
"VENDOR_NO",
"TAX_AMOUNT_BASE",
"INVOICING_SUPPLIER",
STATE
FROM "QLIK_V2".dbo."PO_Lines";
LIB CONNECT TO 'SQL CONNECTION';
LOAD TRANSACTION_ID,
PART_NO as [component_part],
CONTRACT,
COST AS [TRANSACTION_COST],
SOURCE_REF1,
TRANSACTION,
DATE_APPLIED,
YEAR(DATE_APPLIED) AS [STAT_YEAR_NO],
Month(DATE_APPLIED) AS [STAT_PERIOD_NO],
DIRECTION,
QUANTITY AS [TRANSACTION_QTY],
USERID,
TRANSACTION_CODE;
[inv_transactions_history]:
SELECT "TRANSACTION_ID",
"PART_NO",
CONTRACT,
"COST",
"SOURCE_REF1",
"TRANSACTION",
"DATE_APPLIED",
DIRECTION,
QUANTITY,
USERID,
"TRANSACTION_CODE"
FROM "QLIK_V2".dbo."inv_transactions_history"
where TRANSACTION_CODE in ('BACFLUSH','CO-DELV-OU','INTSHIP','INTUNISS','OESHIP','OEUNSHIP','PODIRSH','RETCORCRE','SOISS','SUNREC','UNISS');