Re: Loading multiples data files in a single scrip... - Qlik Community

Report Inappropriate Content · ‎2014-05-16

I have QlikView personal edition (11.2) on my local machine. I am trying to load multiple xls data files (5 tables) in a single script but it runs for ever and doesn't finish. I would like to test this QlikView functionality as part of my POC plan. Can someone please help me?

file1 - 10 mb

file2 - 124 mb

file3 - 61 kb

file4 - 19 mb

file4 - 3 mb

-----------------------------------------------------Script-------------------------------------------------------------

w_busn_location_d:

LOAD ROW_WID,

GEO_WID,

ADDRESS_TYPE,

BUSN_LOC_NAME,

BUSN_LOC_NUM,

BUSN_LOC_TYPE,

CONTACT_NAME,

CONTACT_NUM,

PARENT_LOC_NAME,

PARENT_LOC_NUM,

ST_ADDRESS1,

ST_ADDRESS2,

CITY_NAME,

POSTAL_CODE,

COUNTY,

STATE_CODE,

STATE_NAME,

STATE_REGION,

COUNTRY_CODE,

COUNTRY_NAME,

COUNTRY_REGION,

PHONE_NUM,

FAX_NUM,

EMAIL_ADDRESS,

WEB_ADDRESS,

ACTIVE_FLG,

CREATED_BY_WID,

CHANGED_BY_WID,

CREATED_ON_DT,

CHANGED_ON_DT,

AUX1_CHANGED_ON_DT,

AUX2_CHANGED_ON_DT,

AUX3_CHANGED_ON_DT,

AUX4_CHANGED_ON_DT,

SRC_EFF_FROM_DT,

SRC_EFF_TO_DT,

EFFECTIVE_FROM_DT,

EFFECTIVE_TO_DT,

DELETE_FLG,

CURRENT_FLG,

W_INSERT_DT,

W_UPDATE_DT,

DATASOURCE_NUM_ID,

ETL_PROC_WID,

INTEGRATION_ID,

SET_ID,

TENANT_ID,

X_CUSTOM,

GEO_COUNTRY_WID,

X_SITE,

X_LEVEL1,

X_LEVEL2,

X_LEVEL3,

X_LEVEL4,

X_LEVEL5,

X_LEVEL6

FROM

$(vSourceData)busn.xls

(biff, embedded labels, table is xx$);

STORE w_busn_location_d into $(vSaveQVD)w_busn_location_d.qvd(qvd);

w_product_d:

LOAD DB_ID,

DB_NAME,

UOM,

BASE_UOM_CODE,

BASE_UOM_DESC,

UNIT_GROSS_WEIGHT,

UNIT_NET_WEIGHT,

UNIT_VOLUME,

UNIV_PROD_CODE,

UNIV_PROD_DESC,

UOV_CODE,

UOV_DESC,

UOW_CODE,

UOW_DESC,

APPLICATION_FLG,

BODY_STYLE_CD,

CASE_PACK,

CTLG_CAT_ID,

DEALER_INV_PRICE,

DEALER_INV_PRICE_EXCH_DT,

DEALER_INV_PRICE_CURCY_CD,

DETAIL_TYPE,

DOORS_TYPE_CD,

DRIVE_TRAIN_CD,

ENGINE_TYPE_CD,

FUEL_TYPE_CD,

GROSS_MRGN,

INVENTORY_FLG,

MAKE_CD,

MODEL_CD,

MODEL_YR,

MSRP,

MSRP_EXCH_DT,

MSRP_CURCY_CD,

ORDERABLE_FLG,

PROD_NDC_ID,

PROD_TYPE,

PROFIT_RANK,

REFERRAL_FLG,

RX_AVG_PRICE,

RX_AVG_PRICE_EXCH_DT,

RX_AVG_PRICE_CURCY_CD,

SERVICE_TYPE,

STATUS,

R_TYPE,

SUB_TYPE,

SUB_TYPE_CD,

TGT_CUST_TYPE,

TRANSMISSION_CD,

TRIM_CD,

UNIT_CONV_FACTOR,

VER_DT,

PAR_INTEGRATION_ID,

PROD_HIER1_CODE,

PROD_HIER1_NAME,

PROD_HIER2_CODE,

PROD_HIER2_NAME,

PROD_HIER3_CODE,

PROD_HIER3_NAME,

PROD_HIER4_CODE,

PROD_HIER4_NAME,

PROD_HIER5_CODE,

PROD_HIER5_NAME,

PROD_HIER6_CODE,

PROD_HIER6_NAME,

CREATED_BY_ID,

CHANGED_BY_ID,

CREATED_ON_DT,

CHANGED_ON_DT,

AUX1_CHANGED_ON_DT,

AUX2_CHANGED_ON_DT,

AUX3_CHANGED_ON_DT,

AUX4_CHANGED_ON_DT,

SRC_EFF_FROM_DT,

SRC_EFF_TO_DT,

DELETE_FLG,

DATASOURCE_NUM_ID,

INTEGRATION_ID,

SET_ID,

TENANT_ID,

X_CUSTOM,

PROD_NAME,

PRODUCT_TYPE_CODE,

PRODUCT_TYPE_DESC,

PR_PROD_LN,

PR_PROD_LN_DESC,

CONFIG_CAT_CODE,

CONFIG_CAT_DESC,

PRICE_TYPE_CD,

BASIC_PRODUCT,

PR_EQUIV_PROD_NAME,

CONFIG_PROD_IND,

CONTAINER_CODE,

CONTAINER_DESC,

INDUSTRY_CODE,

INDUSTRY_NAME,

INDUSTRY_STD_DESC,

INTRODUCTION_DT,

LOW_LEVEL_CODE,

MAKE_BUY_IND,

PROD_LIFE_CYCL_CD,

PROD_REPRCH_PERIOD,

PROD_STRUCTURE_TYPE,

PROD_GRP_CODE,

PROD_GRP_DESC,

STORAGE_TYPE_CODE,

STORAGE_TYPE_DESC,

BATCH_IND,

BRAND,

COLOR,

CUSTOM_PROD_FLG,

PACKAGED_PROD_FLG,

RTRN_DEFECTIVE_FLG,

SALES_PROD_FLG,

SALES_SRVC_FLG,

SERIALIZED_FLG,

NRC_FLG,

FRU_FLG,

ITEM_SIZE,

LEAD_TIME,

MTBF,

MTTR,

PART_NUM,

VENDOR_LOC,

VENDOR_NAME,

VENDR_PART_NUM,

DISCONTINUATION_DT,

HAZARD_MTL_DESC,

HAZARD_MTL_CODE,

SALES_UOM_CODE,

SALES_UOM_DESC,

SERIALIZED_COUNT,

SHELF_LIFE,

SHIP_MTHD_GRP_CODE,

SHIP_MTHD_GRP_DESC,

SHIP_MTL_GRP_CODE,

SHIP_MTL_GRP_DESC,

SHIP_TYPE_CODE,

SHIP_TYPE_DESC,

SOURCE_OF_SUPPLY,

SPRT_WITHDRAWL_DT

FROM

$(vSourceData)file_product01.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

STORE w_product_d into $(vSaveQVD)w_product_d.qvd(qvd);

wc_customer_d:

LOAD DB_ID,

DB_NAME,

CUSTOMER_ID,

COMPANY_NAME,

SORT_NAME,

X_CustStatus,

CREATED_ON_DT,

CHANGED_BY_ID,

CHANGED_ON_DT,

X_Customer_Class,

X_AccountFocus,

CUSTOMER_CHANNEL,

SALESPERSON1,

SALESPERSON2,

SALESPERSON3,

SALESPERSON4,

X_KwiksetPfisterRep,

X_BaldwinRep,

X_SNHRep,

X_KwiksetCanRep,

X_PfisterCanRep,

X_NAMKAM,

X_AccountOwner,

ADDRESS_TITLE,

ST_ADDRESS1,

ST_ADDRESS2,

ST_ADDRESS3,

CITY,

COUNTY,

POSTAL_CODE,

STATE_CODE,

STATE_NAME,

STATE_REGION,

COUNTRY_CODE,

PHONE_NUM,

FAX_NUM,

EMAIL_ADDRESS,

WEB_ADDRESS,

AUTO_ROUTING_CODE,

PRMRY_CNTCT_NAME,

X_CustomerHold,

PAY_TERMS_CODE,

PAY_TERMS_NAME,

CRRATING_CODE,

CUST_CREDIT_LIMIT,

RECCRLIMIT_AMT,

X_CustRemarks,

X_CustShipVia,

X_CustLastSale,

X_CustLastPayMent,

X_CustPartial,

X_Division,

X_ClientRegion,

X_RegionName,

X_District,

X_DC,

X_Member,

X_MarktAdv_PatchArea,

X_Psion_CustNumb,

X_Sort_Seq,

X_Parent,

[X_Netprice Decimal],

X_LTtype,

X_BillTo,

X_Warehouse,

DELETE_FLG,

DATASOURCE_NUM_ID,

INTEGRATION_ID,

X_custdefaultsite,

X_custstorenumber

FROM

$(vSourceData)file_customer01.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

STORE wc_customer_d into $(vSaveQVD)wc_customer_d.qvd(qvd);

w_customer_loc_d:

LOAD DB_ID,

DB_NAME,

CHANGED_ON_DT,

AUX1_CHANGED_ON_DT,

AUX2_CHANGED_ON_DT,

AUX3_CHANGED_ON_DT,

AUX4_CHANGED_ON_DT,

SRC_EFF_FROM_DT,

SRC_EFF_TO_DT,

DELETE_FLG,

DATASOURCE_NUM_ID,

INTEGRATION_ID,

SET_ID,

TENANT_ID,

X_CUSTOM,

CUSTOMER_ID,

ADDRESS_TITLE,

ST_ADDRESS1,

ST_ADDRESS2,

CITY,

COUNTY,

POSTAL_CODE,

STATE_CODE,

STATE_NAME,

STATE_REGION,

COUNTRY_CODE,

COUNTRY_NAME,

COUNTRY_REGION,

PHONE_NUM,

FAX_NUM,

EMAIL_ADDRESS,

WEB_ADDRESS,

AUTO_ROUTING_CODE,

ADDR_LATITUDE,

ADDR_LONGITUDE,

PRMRY_CNTCT_NAME,

ACTIVE_FLG,

CREATED_BY_ID,

CHANGED_BY_ID,

CREATED_ON_DT

FROM

$(vSourceData)file_customer_location01.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

STORE w_customer_loc_d into $(vSaveQVD)w_customer_loc_d.qvd(qvd);

w_sales_invoice_line_f:

LOAD DB_ID,

DB_NAME,

SALES_ORDER_NUM,

INVOICE_NUM,

INVOICE_STATUS_ID,

SALES_ORDER_ITEM_DETAIL_NUM,

X_Release_ID,

PURCH_ORDER_NUM,

ORDER_SOURCE_ID,

ORDER_STATUS_ID,

SHIPPABLE_FLG,

SHIP_PARTIAL,

BOOKING_FLG,

X_CONFIRMED,

EARLY_SHIPMENTS_ALLOWED_FLG,

BILLING_FLG,

DELETE_FLG,

REJECT_FLG,

X_Sales_Order_Status,

X_READY_TO_INVOICE,

X_ORDER_INVOICED,

CUSTOMER_BILL_TO_LOC_ID,

CUSTOMER_SHIP_TO_LOC_ID,

CUSTOMER_SOLD_TO_LOC_ID,

CHNL_TYPE_ID,

X_SALES_REP1_CODE,

X_SALES_REP2_CODE,

X_SALES_REP3_CODE,

X_SALES_REP4_CODE,

X_KwiksetPfisterRep,

X_BaldwinRep,

X_SNHRep,

X_KwiksetCanRep,

X_PfisterCanRep,

X_NAMKAM,

X_AccountOwner,

X_Part_Number_Lv1,

X_Part_Number_Lv0,

PROD_LINE,

X_Product_Brand,

X_Customer_ProductID,

BOOKED_ON_DT,

CANCELLED_ON_DT,

CONFIRMED_ON_DT,

X_CONFIRMED_ON_TIME,

X_PERFORMANCE_DATE,

ENTERED_ON_DT,

INVENTORY_DEMAND_DT,

PROMISED_ON_DT,

REQUIRED_BY_DT,

FULFILLED_ON_DT,

INVOICED_ON_DT,

X_HHIONTIMEDATE,

X_Release_DT,

X_Xmit_Date,

X_Xmit_Date_Time,

X_Order_Load_Time,

X_Credit_Release_Date,

X_Credit_Release_Time,

X_Unit_COST_AMT,

X_UNIT_INV_AMT,

X_UNIT_LIST_AMT,

X_UNIT_DISCOUNT_AMT,

DOC_CURR_CODE,

X_DOC_EXCH_RATE_TYPE,

X_DOC_EXCHANGE_RATE,

LOC_CURR_CODE,

LOC_EXCH_RATE_TYPE,

LOC_EXCHANGE_RATE,

ORDERED_QTY,

X_Qty_Allocated,

TOTAL_SHIPPED_QTY,

TOTAL_INVOICED_QTY,

SALES_UOM_CODE,

SHIPPING_LOC_ID,

X_Site,

SALES_ACCT,

DISCOUNT_ACCT,

COST_CENTER_ID,

X_Project_ID,

REF_DOC_NUM,

SUPPLIER_ID,

PAYMENT_TERMS_ID,

FREIGHT_TERMS_ID,

X_Shipvia,

X_Sales_Order_Comment_Type,

X_Sales_Order_Print_Packlist,

X_Backorder,

X_Price_list,

X_Scenario,

CREATED_BY_ID,

CHANGED_BY_ID,

CHANGED_ON_DT,

INTEGRATION_ID,

DATASOURCE_NUM_ID,

X_Order_Load_Date,

X_backorder_qty,

X_OffInvDiscount_Percent,

X_SHIPMENT_TRACKING_NUMBER,

X_SO_REMARKS,

X_SO_MEMO_TYPE,

X_ITEM_DESC

FROM

$(vSourceData)file_sales_invoice01.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

STORE w_sales_invoice_line_f into $(vSaveQVD)w_sales_invoice_line_f.qvd(qvd);

------------------------------------------------------------------------------------------------------------------------

Report Inappropriate Content · ‎2014-05-16

I think you are running out of memory, After every store drop the table

example

STORE w_busn_location_d into $(vSaveQVD)w_busn_location_d.qvd(qvd);

Drop table w_busn_location_d;

jaimeaguilar · ‎2014-05-16

Hi,

the problem here is that you're trying to load multiple tables that have many fields in common. This will generate many synthetic keys that will affect QlikView performance. In QlikView, Relationships between tables are made by field name. You can read more about that here: http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

As a debug practice you can use qualify statement which will rename all the fields you select, adding a preffix with the name of the source table. So in this case you want to rename all fields except key fields.

you can do something like this:

Qualify *;

unqualify keyfield1,keyfield2, etc;

table1:

load

field1,

field2,

fieldn

from table1;

table2:

load

field1,

field2,

fieldn

from table2;

Unqualify *;

this way you can check table per table if fields with the same name contain indeed the same information or they just sharing a same name,

regards

Joseph_Musekura · ‎2014-05-16

Dear Sir,

I agree with Jaime.

You could also avoid Synthetic Keys by renaming the fields with the same names

Example:

T1:

Load

F1,

F2,

F3,

F4

From xxxxx;

T2:

Load

F1,

F2 as somthingF2,

F3 as anythingF3,

F5

From xxxxx;

Regards,

Joseph_Musekura · ‎2014-05-16

Hi again,

If your data is not sensitive, you could attach the 4 files for test.

Regards,

Report Inappropriate Content · ‎2014-05-17

Hi Tarun, Seems to be you are running some memory problems. Drop the table after store as QVD to release the memory free.

STORE w_busn_location_d into $(vSaveQVD)w_busn_location_d.qvd(qvd);

DROP Table w_busn_location_d ;

hic · ‎2014-05-18

One good way to debug, is to load only a very small number of records from each file, e.g. by using

First 5 LOAD ...

instead of the standard LOAD. Then you can look at the data model (ctrl-T) and start to think what you should do to fix it.

HIC

its_anandrjs · ‎2014-05-18

I suggest go for QVD maker of this files and make the QVD and then load all this QVD in the another application but here load minimum data in the application or debug the application with minimum data load and remove the synthetic keys. if possible share the raw files. See the QVD maker script.

w_busn_location_d:

LOAD *

FROM

$(vSourceData)busn.xls

(biff, embedded labels, table is xx$);

STORE w_busn_location_d into $(vSaveQVD)w_busn_location_d.qvd(qvd);

DROP Table w_busn_location_d;

w_product_d:

LOAD *

FROM

$(vSourceData)file_product01.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

STORE w_product_d into $(vSaveQVD)w_product_d.qvd(qvd);

DROP Table w_product_d;

wc_customer_d:

LOAD *

FROM

$(vSourceData)file_customer01.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

STORE wc_customer_d into $(vSaveQVD)wc_customer_d.qvd(qvd);

DROP Table wc_customer_d;

w_customer_loc_d:

LOAD *

FROM

$(vSourceData)file_customer_location01.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

STORE w_customer_loc_d into $(vSaveQVD)w_customer_loc_d.qvd(qvd);

DROP Table w_customer_loc_d;

w_sales_invoice_line_f:

LOAD *

FROM

$(vSourceData)file_sales_invoice01.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

STORE w_sales_invoice_line_f into $(vSaveQVD)w_sales_invoice_line_f.qvd(qvd);

DROP Table w_sales_invoice_line_f;

Report Inappropriate Content · ‎2014-05-22

Thanks everyone for your response. I was able to solve the issue by drop temporary tables. Thanks again

Loading multiples data files in a single script doesn't work