Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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

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

Tags (3)
8 Replies
Not applicable

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

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
Valued Contributor II

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

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

Support
Support

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

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,

Support
Support

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

Hi again,

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

Regards,

Not applicable

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

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 ;


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

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

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

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;

Not applicable

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

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