2.2 Load Scripts (Client Managed)

As briefly highlighted in last section (2.4) of the Installation Guide there are essentially four ways that the Load Script can be set-up depending on the combination of information that you want to reload from the Data and History tables in the Writeback SQL database.

In this article, we outline some BEST PRACTICES for setting up the load script, and provide three examples for reference:

3.1. Defining User Access

Originally User Access was defined inside the Load Script, however this is now defined in the extension GUI.

***Note on Section Access***: The service account running the API (eg. sa_api) must be included in the section access document so that the account can access the app to execute the partial reload.

3.2 Load Statements (a reminder!)

Whilst also documented in the Installation Guide, it is worth getting a reminder of the three Load Statements need to use when setting p our load script:

3.2.1 The REPLACE Statement

• The replace statement will execute on a full and partial reload.
• If it is pre-fixed with the word ‘only’ then it will only execute on partial reloads.
• When a replace statement is read, Qlik will drop any table of the same name already held in memory before executing the load statement to recreate it.
• The replace load statement can become un-stable when re-indexing into a complex data model.
• When the table being ‘replaced’ is stand-alone, the replace statement can be an efficient option to use
• A common alternative to ‘Replace’ is ‘ISPartialReload() then drop table’.

3.2.2 The ADD Statement

• The add statement will execute on a full and partial reload.
• If it is pre-fixed with the word ‘only’ then it will only execute on partial reloads.
• It is best used in conjunction with a ‘drop table’ statement, or in instances where you only want to ‘add’ to a table that is already held in memory, as opposed to replacing the data values.
• It is best practice to first drop the table during a partial reload and then add it back in using the ADD prefix.

3.2.3 The ISPartialreload() Statement

• Use the function ISPartialReload() for the script to pick up if it needs to drop a table and conditionally add in the partial reloaded table.
• It can be used to execute any other function, such as ‘store’, ‘drop’, ‘keep’ etc.
• The function returns (-1) if true
• The conditional isPartialReload() function should be at the front of the script followed by the tables to be dropped and then added back in. Once added back in with the ADD LOAD statement there should be an exit if isPartialReload () statement. The script needs to be at the front of the application because during a partial reload, Qlik still reads all of the load script but only loads what the user had given an ADD prefix to the load.
• This means that the load will fail when it cannot find a table that was dropped during the full load process.
• This will result in a successful commit to the DB, but the user will not see the data come into Qlik until a full reload is performed.

3.3. Working the Table(s) into the Data Models

The table should be limited one index/ association when being built into the data model. In other words, it should only link to one table!

***NOTES***:

• This method speeds up the partial reload time by limiting the number of times the reloaded table requires a new association or index
  • eg. Make and rename a duplicate key that only works for the table that goes through the partial reload.
• Not using this method has led to engine stoppages when dealing with complex data models and large volumes of data!

3.4. Script Examples

The following examples are based on having a transaction table. If you have an alternative use case that you would like us to document, please contact your Pomerol Consultant or Sales Agent.

3.4.1. Example 1

Latest writeback records are saved to the transaction table. Historical records are saved to a different table with different fieldnames.

TRANSACTIONS_TEMP: // Preceding loads like this can be accommodated (make sure the 'replace' function is in the last step of the preceding load).

REPLACE LOAD *;

// Example data
 LOAD * INLINE [
 id_1,  Field_A, Field_B, Field_C, Field_D, Field_E
 1,   A,   A, A, Approved,  20
 2,    A, A, A,  Approved,   40
 3,    A, A, A,  Approved,   35
 4,    A, A, A,  Approved,   100
 5,    A, A, A,  Approved,   10
 6,    A, A, A,  Approved,   20
 7,    A, A, A,  Approved,   30
 8,    A, A, A,  Approved,  25];

// Establish connection to the SQL DB that we are writing to and loading from.
LIB CONNECT TO 'MSSQL QSWE_DEMO (pomerol_lindy.brits)';

// During a partial reload join the edited values onto the source DB data so that we can identify what has been edited.
LEFT JOIN(TRANSACTIONS_TEMP)
REPLACE LOAD 
        id_1, 
        Field_A       AS      Field_A_x, 
        Field_B       AS      Field_B_x, 
        Field_C       AS      Field_C_x, 
        Field_D       AS      Field_D_x, 
        Num(Field_E)    AS      Field_E_x, 
               TS, 
               QlikUser,
    'true'          AS  TS_Flag; // create the 'true' flag as these records are the latest versions (loaded from the 'Data' table)
SELECT "id_1",
              "Field_A",
              "Field_B",
              "Field_C",
              "Field_D",
              "Field_E",
              TS,
              QlikUser

FROM "QSWE_DEMO".dbo."Data_1";

// Compare the source DB values to the writeback DB values. If writeback values exist for a given Id, keep them, otherwise show the original values.
TRANSACTIONS_FINAL:
REPLACE LOAD
 id_1, 
    If(IsNull(Field_A_x),Field_A,Field_A_x)  AS  Field_A,
    If(IsNull(Field_B_x),Field_B,Field_B_x)  AS  Field_B,
    If(IsNull(Field_C_x),Field_C,Field_C_x)  AS  Field_C,
    If(IsNull(Field_D_x),Field_D,Field_D_x)  AS  Field_D,
    If(IsNull(Field_E_x),Field_E,Field_E_x)  AS  Field_E,
TS, 
QlikUser,
TS_Flag
RESIDENT TRANSACTIONS_TEMP;
DROP TABLE TRANSACTIONS_TEMP;

// load the history table and the data table - this will give us a full set of all records in the writeback DB. 
// we're pulling the full history table with each partial reload here, we can also store the hsitory locally
// each time a partial reload is executed - this will prevent the full table being loaded each time.
// architecture here is very flexible depending on the use case and data volumes.

TRANSACTIONS_HISTORY:
REPLACE LOAD

 id_1, 
 Field_A  AS  Field_A_History, 
 Field_B  AS  Field_B_History, 
 Field_C  AS  Field_C_History, 
 Field_D  AS  Field_D_History, 
 Num(Field_E)  AS  Field_E_History, // 'Numming' this field as it is stored as text in the DB
 TS  AS  TS_History,
 QlikUser  AS  QlikUser_History,
    'false'  AS TS_Flag_History; // create the 'false' flag as these records are not the latest versions
SELECT "id_1",
 "Field_A",
 "Field_B",
 "Field_C",
 "Field_D",
 "Field_E",
 TS,
 QlikUser
FROM "QSWE_DEMO".dbo."History_1";

CONCATENATE(TRANSACTIONS_HISTORY)
ADD LOAD 
 id_1, 
 Field_A  AS  Field_A_History, 
 Field_B  AS  Field_B_History, 
 Field_C  AS  Field_C_History, 
 Field_D  AS  Field_D_History, 
 Num(Field_E)  AS  Field_E_History, // 'Numming' this field as it is stored as text in the DB
 TS  AS  TS_History, 
 QlikUser  AS  QlikUser_History,
    'true'  AS TS_Flag_History; // create the 'true' flag as these records are the latest versions
SELECT "id_1",
 "Field_A",
 "Field_B",
 "Field_C",
 "Field_D",
 "Field_E",
 TS,
 QlikUser
FROM "QSWE_DEMO".dbo."Data_1";

3.4.2. Example 2

Transaction table is dropped fully on each partial reload. Latest writeback records are saved to the transaction table. Historical records are not loaded. A left join is used to add the latest edited records to the transaction table. We then look at each field to see if a writeback value exists, if it does we keep it, if it doesn’t then we show the original value.

// If we are conducting a Partial Reload then drop the table [Product Mapping]
IF IsPartialReload() THEN; DROP TABLE [Product Mapping]; END IF;

// Load the original source data, this will also be loaded during a partial reload
// This data set contains some 'mapped' and some 'unmapped' records
// In a reall world  scenario this would be coming from multiple systems collating live product data (ERP, CRM etc.)

[Product Mapping Temp]:
ADD LOAD
    Record_ID,
    Country,
    Source,
    ApplyMap('Data_Type_Mapping',Source,'Unknown') 		AS [Data Type],
    Product_Code,
    Product_Description,
    Country&'*'&Source 									AS DataStewardKey,
    Brand,
    Strength,
    Format,
    Pack_Size,
    Channnel,
    Key,
    Product_Mapped_Flag,
    Product_Mapping_Status, 		
    '' AS Comments					
    
FROM [lib://Product MDM for Pharma/Pharma Product MDM Data Set.xlsx]
(ooxml, embedded labels, table is [Product Mapping]);
 

// During the full and partial reloads load the latest edited data from the writeback database

LIB CONNECT TO 'MSSQL QSWE_DEMO';

LEFT JOIN([Product Mapping Temp])
ADD LOAD
[Record ID] 		AS Record_ID,
[Brand] 		AS [Brand QSWE],
[Strength] 		AS [Strength QSWE],
[Format] 		AS [Format QSWE],
[Pack Size] 		AS [Pack_Size QSWE],
[Channnel] 		AS [Channnel QSWE],
[Status] 		AS [Product_Mapping_Status QSWE],
[Comments] 		AS [Comments QSWE],
[Brand]&' '&[Strength]&' '&[Format]&' '&[Pack Size]&' '&[Channnel] 	AS [Key QSWE],  
[TS] 			AS [TS],
[QlikUser] 		AS [QlikUser],
 'true' 			AS TS_Flag;
SQL SELECT 
	"Record ID", 
	Brand, 
	Strength, 
	Format, 
	"Pack Size", 
	Channnel, 
	Status, 
	Comments, 
	TS, 
	QlikUser, 
	"TS_Flag" 
FROM "QSWE_DEMO".dbo."Pharma_MDM_QSWE_Data";

[Product Mapping]:
NOCONCATENATE
ADD LOAD
	Record_ID 																		AS [Record ID],
    Country,
    Source,
    [Data Type],
    Product_Code,
    Product_Description, 
    DataStewardKey,
    If(Len([Brand QSWE])>0,'UNMAPPED',Product_Mapped_Flag) 							AS Product_Mapped_Flag,	
	If(Len([Brand QSWE])>0,[Brand QSWE],[Brand]) 									AS Brand,
If(Len([Brand QSWE])>0,[Strength QSWE],[Strength]) 					AS Strength,
If(Len([Brand QSWE])>0,[Format QSWE],[Format]) 					AS Format,
If(Len([Brand QSWE])>0,[Pack_Size QSWE],[Pack_Size]) 	 				AS [Pack Size],
If(Len([Brand QSWE])>0,[Channnel QSWE],[Channnel]) 					AS Channnel,
If(Len([Brand QSWE])>0,[Product_Mapping_Status QSWE],[Product_Mapping_Status]) 	AS Status,
If(Len([Brand QSWE])>0,[Comments QSWE],[Comments]) 				AS Comments,
If(Len([Brand QSWE])>0,[Key QSWE],Key) 						AS Key,
[TS] 											AS QSWE_TS,
[QlikUser] 									AS QSWE_QlikUser,
TS_Flag

Resident [Product Mapping Temp];
Drop Table [Product Mapping Temp];

3.4.3. Example 3

Transaction table is separate. The writeback fields are different to those found in the original transactions. The writeback latest records table and the history table are concatenated. A TS_Flag is used to identify the latest vs historical records.

// Load raw transaction data from the ERP system.
// Rename fields to remove spaces - this aligns with the format required in the writeback DB
TRANSACTIONS:
LOAD
    [Transaction ID] 					AS Transaction_ID,
    [Transaction Date] 					AS Transaction_Date,
    [Transaction Date Num] 				AS Transaction_Date_Num,
    [Customer ID] 						AS Customer_ID,
    [Gross Amount] 						AS Gross_Amount
FROM [lib://QS Writeback Data Folder/QS Writebasck API - Demo Data.xlsx]
(ooxml, embedded labels, table is [Sales Transactions]);

// Join the master data for customer and contract max discounts.
Left Join(TRANSACTIONS)
LOAD DISTINCT
    [Customer ID] 						AS Customer_ID,
    [Company Name] 						AS Company_Name,
    [Contract Discount] 				AS Contract_Discount,
    [Customer Name] 					AS Customer_Name,
    [Sales Rep] 						AS Sales_Rep 
FROM [lib://QS Writeback Data Folder/QS Writebasck API - Demo Data.xlsx]
(ooxml, embedded labels, table is [Customer Master]);

// After a partial reload from the writeback database we'll need to check again that the discount alloacted to a customer does not exceed the conttacted maximum.
// This mapping load will let us look up the contracted max discounts during the partial reload.

Contract_Discount_Mapping:
REPLACE
Mapping Load
Transaction_ID,
Contract_Discount
Resident TRANSACTIONS;

// Load the latest edited tranactions from the writeback database - these transactions have a TS_Flag of 'true'.
// This will execute on a full and partial reload.

WRITEBACK_DB:
REPLACE
LOAD
	Transaction_ID,
    Num(Clawback_Percentage) AS Clawback_Percentage, 
    If(Clawback_Percentage>0 and Clawback_Percentage>ApplyMap('Contract_Discount_Mapping',Transaction_ID,Null()) and Len(Narrative)<=1, 'Narrative Required',Narrative) AS Narrative,	
    If(Clawback_Percentage > ApplyMap('Contract_Discount_Mapping',Transaction_ID,Null()),'Over',
    If(Clawback_Percentage = ApplyMap('Contract_Discount_Mapping',Transaction_ID,Null()),'Neutral',
    If(Clawback_Percentage < ApplyMap('Contract_Discount_Mapping',Transaction_ID,Null()),'Under',Null()))) 	AS Discount_Status,
    If(Len(Approval_Status)<2,'Pending',Approval_Status) 													AS Approval_Status,
    If(Rush_Delivery_Flag='true','Y',If(Rush_Delivery_Flag='false','N','-')) 								AS Rush_Delivery_Flag,
    TS,
    TS_Flag,
    Mid(QlikUser, Index(QlikUser,'=',2)+1) 																	AS QlikUser;
SQL SELECT 
	"Transaction_ID", 
	Narrative, 
	"Clawback_Percentage", 
	"Approval_Status",
    Rush_Delivery_Flag,
	QlikUser, 
	TS, 
	'true' AS "TS_Flag" 
FROM "QSWE_DEMO".dbo."Customer_Discount_QSWE_Data";

// Add the historical edited transactions to the WRITEBACK_DB table - these transactions have a TS_Flag of 'false'.
// This will only execute on a full reload.

CONCATENATE(WRITEBACK_DB)
LOAD
	Transaction_ID,
    Num(Clawback_Percentage) AS Clawback_Percentage, 
    If(Clawback_Percentage>0 and Clawback_Percentage>ApplyMap('Contract_Discount_Mapping',Transaction_ID,Null()) and Len(Narrative)<=1, 'Narrative Required',Narrative) AS Narrative,	
    If(Clawback_Percentage > ApplyMap('Contract_Discount_Mapping',Transaction_ID,Null()),'Over',
    If(Clawback_Percentage = ApplyMap('Contract_Discount_Mapping',Transaction_ID,Null()),'Neutral',
    If(Clawback_Percentage < ApplyMap('Contract_Discount_Mapping',Transaction_ID,Null()),'Under',Null()))) 	AS Discount_Status,
    If(Len(Approval_Status)<2,'Pending',Approval_Status) 													AS Approval_Status,
    If(Rush_Delivery_Flag='true','Y',If(Rush_Delivery_Flag='false','N','-')) 								AS Rush_Delivery_Flag,
    TS,
    TS_Flag,
    Mid(QlikUser, Index(QlikUser,'=',2)+1) 																	AS QlikUser;
SQL SELECT 
	"Transaction_ID", 
	Narrative, 
	"Clawback_Percentage", 
	"Approval_Status",
    Rush_Delivery_Flag,
	QlikUser, 
	TS, 
	'false' AS "TS_Flag" 
FROM "QSWE_DEMO".dbo."Customer_Discount_QSWE_History";

// Add the historical edited transactions to the WRITEBACK_DB table - these transactions have a TS_Flag of 'false'.
// This will only execute on a partial reload.
// This is the only way to concatenate the Data + History during a partial reload.

If IsPartialReload() Then 
CONCATENATE(WRITEBACK_DB)
ADD LOAD
	Transaction_ID,
    Num(Clawback_Percentage) AS Clawback_Percentage, 
    If(Clawback_Percentage>0 and Clawback_Percentage>ApplyMap('Contract_Discount_Mapping',Transaction_ID,Null()) and Len(Narrative)<=1, 'Narrative Required',Narrative) AS Narrative,	
    If(Clawback_Percentage > ApplyMap('Contract_Discount_Mapping',Transaction_ID,Null()),'Over',
    If(Clawback_Percentage = ApplyMap('Contract_Discount_Mapping',Transaction_ID,Null()),'Neutral',
    If(Clawback_Percentage < ApplyMap('Contract_Discount_Mapping',Transaction_ID,Null()),'Under',Null()))) 	AS Discount_Status,
    If(Len(Approval_Status)<2,'Pending',Approval_Status) 													AS Approval_Status,
    If(Rush_Delivery_Flag='true','Y',If(Rush_Delivery_Flag='false','N','-')) 								AS Rush_Delivery_Flag,
    TS,
    TS_Flag,
    Mid(QlikUser, Index(QlikUser,'=',2)+1) 																	AS QlikUser;
SQL SELECT 
	"Transaction_ID", 
	Narrative, 
	"Clawback_Percentage", 
	"Approval_Status",
    Rush_Delivery_Flag,
	QlikUser, 
	TS, 
	'false' AS "TS_Flag" 
FROM "QSWE_DEMO".dbo."Customer_Discount_QSWE_History";  
End If;

// Link a log table that counts the number of unique revisions for each transaction.
// This will execute on a full and partial reload.

TRASACTION_REVISIONS_LOG:
REPLACE
LOAD
	Transaction_ID, 																		    
    Count(Transaction_ID) AS Revisions 
RESIDENT WRITEBACK_DB
Group By Transaction_ID;