Help with load script optimizing

jalanhart · ‎2024-02-05

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');

henrikalmen · ‎2024-02-05

I can't read through all of the script right now, but you say "I'm assuming it's crashing as it's trying to make more synthetic keys." If so, your solution is to get rid of synthetic keys. You should not have those. You do that by renaming fields so that there is not more than one field that links two tables together. If you need more than one field to be the link you should create a composit key. You can read more here: https://help.qlik.com/en-US/sense/Subsystems/Hub/Content/Sense_Hub/Scripting/synthetic-keys.htm

jalanhart · ‎2024-02-05

thanks, synthetic keys have been working great overall for me until now with more data across more tables.

Each of our tables needs to have two fields (contract and part_no) other wise the data has not been matching up like i need it to. then to match another table which doesn't have conventional dates, I needed to create a field that extracts the year out of the dates and the same for month. then I had QLIK automatically creating keys that matched part and contract together along with the date extracts. This is working fine for now with what I'm trying to accomplish, but the problem is that I need to change some of the fields to a different one and then it freaks out and aborts. here is my current model.

henrikalmen · ‎2024-02-06

Synthetic keys rarely work great. Always avoid them unless you really know what you're doing and explicitly want them for some reason (but I can't really see what that reason would be, other than trying to save time when importing data). Read more on this here: https://community.qlik.com/t5/Design/Synthetic-Keys/ba-p/1472634

I believe you should resolve those synthetic keys by generating new key fields, for example hash128(CONTRACT, PART_NO, STAT_YEAR_NO, STAT_PERIOD_NO) as %contractPartStatKey in the tables PO_Lines, inv_value_per_period, inv_transactions_history_invoice_statistics, and remove (or rename) those original fields so that you have only one field that links them together. That's what's called a composite key.

jalanhart · ‎2024-02-06

Thanks for your help, I will play around with this later today.

right now with those fields all as part_no the model is working pretty good, whether synthetic keys are the right approach or not. I was able to make a concatenated field of year and period for all those tables so that eliminated a field which is good.

but my problem now is that I want to change the part_no fields in PO-Lines, Inv_transaction_history, and usage_by_part tables to say component_part instead of part _no. But when I edit the script to load part_no as component_part in those tables the load script just crashes every time.

basically how I’d like the flow to go is you have the invoices table connecting to the WVCO inventory table by contract and part _no. Then the component_part field would connect to the usage by part, PO, and transaction history tables, and those three are also connected along with the invoices by a common date field. These also need to be connected by a contract and component _part combo

i know that’s messy and most likely will be doing this on the SQL db side, but it’s just annoying to me at the moment that QLIK can’t seem to inherently handle what I’m trying to do.

henrikalmen · ‎2024-02-06

I'm not sure, but it's probably because you have a field named component_part in the wwco_inventory table, and it could be that the new synthetic key that is generated when rnaming PART_NO to component_part makes Qlik crash for some reason. The solution is probably to avoid synthetic keys.

jalanhart · ‎2024-02-06

OK so I was looking in to the composite key and was able to mostly get them to run the script, eliminating most of them. but that's not ideal either.

The problem is that I can't filter on either contract or part as an individual field which I need to be able to do. is there a way that I can create composite keys and still filter them as separate fields? Because a composite key does not let me do that.

thanks for your help!

henrikalmen · ‎2024-02-07

Yes of course! Keep the fields contract, part etc in one of the tables (the table where most of those values reside, if there are differences between tables). The problem with synthetic keys occur when there are multiple fields linking tables, but now that you have a composite key to link the tables you can keep the original fields in one of the tables and use those fields just as you're used to.

jalanhart · ‎2024-02-07

thanks again. Where I am at is that I can get rid of the synthetic keys for the most part, only to run in to circular references, and then trying to fix those means my data is not working out in visuals.

Here is the essence of my issue and hoping you can give advice! Tables and fields that I am trying to link up are:

TABLE: Invoices

fields: Contract, part_no, YEARMONTH

TABLE: Inventory Parts

fields: Contract, part_no

TABLE: WVCO Inventory

fields: contract, part_no, component_part

TABLE: POs

fields: contract, part_no AS component_part, YEARMONTH

TABLE: Inventory_transactions

fields: contract, part_no AS component_part, YEARMONTH

TABLE: Inventory_value_per_period

fields: contract, part_no AS component_part, YEARMONTH

this is what makes QLIK freak out and do abort the load, and I'm trying to get rid of the synthetic keys and then I'm getting circular references and it's all very frustrating

Thanks again!

henrikalmen · ‎2024-02-08

I'm not fully following what happens based on this example, but I do want to mention the possibility that you may need multiple compound keys, if different tables share different fields. One compund key might be contract & partno to link some tables, while another compund key may need to be contract & partno & yearmonth to link other tables.

I recommend that you load the first two tables (just add EXIT SCRIPT; in the script where the second table has been loaded) and check your data model. Resolve synthetic keys and circular references that may have occurred. Then you move on and load three tables and check again, after that you load four tables and so on.

General Question