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

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

Fri, 16 May 2014 18:17:44 GMT

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

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

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

Fri, 16 May 2014 18:29:03 GMT

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;

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

jaimeaguilar — Fri, 16 May 2014 18:30:53 GMT

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

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

Joseph_Musekura — Fri, 16 May 2014 18:40:19 GMT

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,

From xxxxx;

T2:

Load

F1,

F2 as somthingF2,

F3 as anythingF3,

From xxxxx;

Regards,

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

Joseph_Musekura — Fri, 16 May 2014 18:44:25 GMT

Hi again,

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

Regards,

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

Sun, 18 May 2014 01:28:48 GMT

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

hic — Sun, 18 May 2014 13:38:26 GMT

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

its_anandrjs — Sun, 18 May 2014 14:35:27 GMT

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;

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

Thu, 22 May 2014 17:17:11 GMT

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