Lessons learned from the Qlikview loads the data from DB to QVD files

Qlikview loads the data in the database, usually through ODBC and the database to connect, and then the database in the corresponding table data loaded into the local disk folder, the latter can be directly from the local disk The Qvd file in folder gets the data to do the dashboard. I start to load the data will always crash, because I load the table data in the good one database to the local QVD file, not in time to delete the corresponding table from memory, resulting in a number of tables in memory, and they will be associated with the same automatic name, and sometimes Qlikview will automatically generate a number of synthetic keys. So it is a good idea to delete the table in memory in time when loading the data.

Here is the data I loaded in the database and save it to an example that would have generated the QVD file:

//loading the data from DB to Local Disk folder
Bi_customer:
LOAD City,
     "customer_id",
    "Customer_industry",
    "Customer_name",
    "Customer_number",
    LOB,
    "Operation_unit",
    "organization_id";
SQL Select city,
    "customer_id",
    "Customer_industry",
     "Customer_name",
    "Customer_number",
    LOB,
     "Operation_unit",
    "organization_id"
from Dw.dbo. " Bi_customer ";
STORE Bi_customer into D:\QLIKVIEW_QVD_NEWDW\DW\BI_CUSTOMER.QVD (QVD);
drop table bi_customer;

bi_customer_anly:
LOAD "Ar_os",
    "customer_id",
    "Customer_name",
    "Customer_number",
    DSO,
     "Dso_ytd",
    "Market_segment",
    MONTH,
     "Operating_unit",
    "ou_id";
SQL Select "Ar_os",
    "customer_id",
    "Customer_name",
     "Customer_number",
    DSO,
    "Dso_ytd",
    " Market_segment ",
    MONTH,
   " Operating_unit ",
   " ou_ ID "
from Dw.dbo." Bi_customer_anly ";

STORE bi_customer_anly into D:\QLIKVIEW_QVD_NEWDW\DW\BI_CUSTOMER_ANLY.QVD (QVD);
drop table bi_customer_anly;

Bi_inv_headers:
LOAD "customer_id",
"Customer_name",
"Customer_number",
"Invoice_date",
"Invoice_number",
Lob
"Operating_unit",
"Organization_id",
"Primary_salesrep_id",
"TRANSACTION_ID";
SQL Select "Customer_ID",
"Customer_name",
"Customer_number",
"Invoice_date",
"Invoice_number",
Lob
"Operating_unit",
"Organization_id",
"Primary_salesrep_id",
"TRANSACTION_ID"
From Dw.dbo. " Bi_inv_headers ";
STORE bi_inv_headers into D:\QLIKVIEW_QVD_NEWDW\DW\BI_INV_HEADERS.QVD (QVD);
drop table bi_inv_headers;

Bi_inv_lines:
LOAD "Ccy_code",
"Ccy_rate",
"Ccy_rate_to_reporting",
COGS,
"Contributed_margin",
"Gross_profit",
"Inventory_item_id",
"Item_Number",
"Market_segment",
"Order_header_id",
"Order_line_id",
"Order_number",
"Order_type",
"Organization_id",
"Primary_quantity",
"Primary_unit_selling_price",
"Primary_uom_code",
"Sales_channel",
"Sales_office_code",
"Sales_region_code",
"Sales_type",
"Salesrep_id",
"Transaction_id",
"Trx_date";
SQL Select "Ccy_code",
"Ccy_rate",
"Ccy_rate_to_reporting",
COGS,
"Contributed_margin",
"Gross_profit",
"Inventory_item_id",
"Item_Number",
"Market_segment",
"Order_header_id",
"Order_line_id",
"Order_number",
"Order_type",
"Organization_id",
"Primary_quantity",
"Primary_unit_selling_price",
"Primary_uom_code",
"Sales_channel",
"Sales_office_code",
"Sales_region_code",
"Sales_type",
"Salesrep_id",
"Transaction_id",
"Trx_date"
From Dw.dbo. " Bi_inv_lines "WHERE trx_date> ' 2012-03-31 ';
STORE bi_inv_lines into D:\QLIKVIEW_QVD_NEWDW\DW\BI_INV_LINES.QVD (QVD);
drop table bi_inv_lines;

Bi_item:
LOAD BRAND,
"CAT_SEG1",
"CAT_SEG2",
COO,
"Inventory_item_id",
"Item_code",
"Item_description",
Lob
Manufacturer,
MODEL,
"Organization_code",
"Organization_id",
SERIES,
"Uom_code";
SQL SELECT BRAND,
"CAT_SEG1",
"CAT_SEG2",
COO,
"Inventory_item_id",
"Item_code",
"Item_description",
Lob
Manufacturer,
MODEL,
"Organization_code",
"Organization_id",
SERIES,
"Uom_code"
From Dw.dbo. " Bi_item ";
STORE Bi_item into D:\QLIKVIEW_QVD_NEWDW\DW\BI_ITEM.QVD (QVD);
drop table Bi_item;

Bi_item_anly:
LOAD DOS,
"Dos_ytd",
"Inventory_item_id",
"Item_Number",
Lob
MONTH,
"Organization_code",
"ORGANIZATION_ID";
SQL SELECT DOS,
"Dos_ytd",
"Inventory_item_id",
"Item_Number",
Lob
MONTH,
"Organization_code",
"ORGANIZATION_ID"
From Dw.dbo. " Bi_item_anly ";
STORE bi_item_anly into D:\QLIKVIEW_QVD_NEWDW\DW\BI_ITEM_ANLY.QVD (QVD);
drop table bi_item_anly;

bi_ou:
LOAD "Ccy_code",
    "organization_id",
     "Ou_code",
    "Ou_name",
    "short_name";
SQL Select "Ccy_code",
    "organization_id",
    "Ou_code",
     "Ou_name",
    "short_name"
from Dw.dbo. " Bi_ou ";
STORE Bi_ou into D:\QLIKVIEW_QVD_NEWDW\DW\BI_OU.QVD (QVD);
drop table Bi_ou;

bi_salesreps:
LOAD "End_date_active",
    "Sales_office_ CODE ",
   " Sales_office_name ",
   " Sales_region_code ",
     "Sales_region_name",
    "salesrep_id",
    "Salesrep_name",
     "Salesrep_number";
SQL Select "End_date_active",
    "Sales_office_code",
    "Sales_office_ NAME ",
   " Sales_region_code ",
   " Sales_region_name ",
     "salesrep_id",
    "Salesrep_name",
    "Salesrep_number"
from Dw.dbo. " Bi_salesreps ";
STORE bi_salesreps into D:\QLIKVIEW_QVD_NEWDW\DW\BI_SALESREPS.QVD (QVD);
drop table bi_salesreps;

Bi_salesreps_anly:
LOAD "Ar_os",
Dso
"Dso_ytd",
MONTH,
"Operating_unit",
"Ou_id",
"Salesrep_id",
"Salesrep_name",
"Salesrep_number";
SQL Select "Ar_os",
Dso
"Dso_ytd",
MONTH,
"Operating_unit",
"Ou_id",
"Salesrep_id",
"Salesrep_name",
"Salesrep_number"
From Dw.dbo. " Bi_salesreps_anly ";
STORE bi_salesreps_anly into D:\QLIKVIEW_QVD_NEWDW\DW\BI_SALESREPS_ANLY.QVD (QVD);
drop table bi_salesreps_anly;

As can be seen from the above, I have finished loading a table and removed the table from memory, which frees up memory for use in subsequent tables.



Lessons learned from the Qlikview loads the data from DB to QVD files