Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

sunil1989
New Contributor

Issue in while storing data into qvd

Hi ,

Thanks in Advance!!!

below is my log file , i am not getting where i am wrong , qlik is giving me error that table not found. can anyone look to log file and help me to find out what is the issue

10/05/2017 09:09:14:      Execution started.

10/05/2017 09:09:14:      QlikView Version:11.20.12904.0

10/05/2017 09:09:14:      CPU Target                    x64

10/05/2017 09:09:14:      Operating System              Windows Server 2008 R2 Enterprise Service Pack 1 (64 bit edition)

10/05/2017 09:09:14:      Wow64 mode                    Not using Wow64

10/05/2017 09:09:14:      MDAC Version                  6.1.7601.17514

10/05/2017 09:09:14:      MDAC Full Install Version     6.1.7601.17514

10/05/2017 09:09:14:      PreferredCompression          2

10/05/2017 09:09:14:      EnableParallelReload          1

10/05/2017 09:09:14:      ParallelizeQvdLoads           1

10/05/2017 09:09:14:      AutoSaveAfterReload           0

10/05/2017 09:09:14:      BackupBeforeReload            0

10/05/2017 09:09:14:      EnableFlushLog                0

10/05/2017 09:09:14:      SaveInfoWhenSavingFile        0

10/05/2017 09:09:14:      UserLogfileCharset            1200

10/05/2017 09:09:14:      OdbcLoginTimeout              -1

10/05/2017 09:09:14:      OdbcConnectionTimeout         -1

10/05/2017 09:09:14:      ScriptWantsDbWrite            false

10/05/2017 09:09:14:      ScriptWantsExe                false

10/05/2017 09:09:14:      LogFile CodePage Used:        1200

10/05/2017 09:09:14:       Reload Executed By PREPROD\extmasys

10/05/2017 09:09:14:       Process Executing: QlikView Desktop

10/05/2017 09:09:14:       Process ID: 16452

10/05/2017 09:09:14: 0003  SET ThousandSep=','

10/05/2017 09:09:14: 0004  SET DecimalSep='.'

10/05/2017 09:09:14: 0005  SET MoneyThousandSep=','

10/05/2017 09:09:14: 0006  SET MoneyDecimalSep='.'

10/05/2017 09:09:14: 0007  SET MoneyFormat='£#,##0.00;-£#,##0.00'

10/05/2017 09:09:14: 0008  SET TimeFormat='hh:mm:ss'

10/05/2017 09:09:14: 0009  SET DateFormat='DD/MM/YYYY'

10/05/2017 09:09:14: 0010  SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]'

10/05/2017 09:09:14: 0011  SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'

10/05/2017 09:09:14: 0012  SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'

10/05/2017 09:09:14: 0014  Let vComputer     = ComputerName()

10/05/2017 09:09:14: 0005  TRACE ---- Computer Name : DEV0772QLIKAP01

10/05/2017 09:09:14: 0005  ---- Computer Name : DEV0772QLIKAP01

10/05/2017 09:09:14: 0007  IF vComputer='WMM0949QLIKAP01'  THEN 

10/05/2017 09:09:14: 0020    SET vQR  = 'P:'

10/05/2017 09:09:14: 0021  ENDIF

10/05/2017 09:09:14: 0026  Let vsPathBigQueryExtract = 'P:\01_QlikView\03 QVD\Shared\BigQuery\Raw\'

10/05/2017 09:09:14: 0028  Let vSourceData = 'P:\01_QlikView\04 Source Data\Projects\Store Pick\'

10/05/2017 09:09:14: 0030  Let vExtractBusinessfileDimQVDPath = 'P:\01_Qlikview\03 QVD\QVDs\Extract\Business File\Dim\'

10/05/2017 09:09:14: 0032  Let vExtractBigQueryDimQVDPath = 'P:\01_QlikView\03 QVD\QVDs\Extract\BigQuery\Dim\'

10/05/2017 09:09:14: 0034  Let vExtractBigQueryFactQVDPath = 'P:\01_QlikView\03 QVD\QVDs\Extract\BigQuery\Fact\'

10/05/2017 09:09:14: 0036  Let vTransformQVDPath   = 'P:\01_QlikView\03 QVD\QVDs\Transform\Store Pick\'

10/05/2017 09:09:14: 0038  Let vExcel = 'P:\01_QlikView\02 Include\Projects\Store Pick\'

10/05/2017 09:09:14: 0040  Let vAuditLog = 'P:\01_QlikView\03 QVD\Projects\Store Pick\RAW\AuditLog\'

10/05/2017 09:09:14: 0043  SET Schema_Name = 'BIA_RTL'

10/05/2017 09:09:14: 0044  SET datasetName= 'store_pick'

10/05/2017 09:09:14: 0046  SET vMYellow = RGB(250,220,65)

10/05/2017 09:09:14: 0047  SET vMGreen = RGB(0,112,80)

10/05/2017 09:09:14: 0022  parm_value:

10/05/2017 09:09:14: 0023  LOAD BatchDate

10/05/2017 09:09:14: 0024  FROM

10/05/2017 09:09:14: 0025  [BatchDate.qvd]

10/05/2017 09:09:14: 0026  (qvd)

10/05/2017 09:09:14:       1 fields found: BatchDate, 1 lines fetched

10/05/2017 09:09:14: 0028  vBatchDate = Peek('BatchDate',0)

10/05/2017 09:09:14: 0030  trace -- 20170508

10/05/2017 09:09:14: 0030  -- 20170508

10/05/2017 09:09:14: 0032  LIST_OF_TABLES:

10/05/2017 09:09:14: 0033  LOAD * INLINE [

10/05/2017 09:09:14: 0034  Connection,TableName,LoadType,StartDate,EndDate,Period,

10/05/2017 09:09:14: 0035  200,store_pick.Payments,1,20170427,20170505,

10/05/2017 09:09:14: 0036  200,store_pick.Refunds,1,20170430,20170505,

10/05/2017 09:09:14: 0037  300,BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY,2,20170427,20170505,Day

10/05/2017 09:09:14: 0038  ]

10/05/2017 09:09:14:       6 fields found: Connection, TableName, LoadType, StartDate, EndDate, Period, 3 lines fetched

10/05/2017 09:09:14: 0045  FOR i=0 to FieldValueCount('TableName') - 1

10/05/2017 09:09:14: 0046    Let vCon =  peek('Connection',0)

10/05/2017 09:09:14: 0047    trace 200

10/05/2017 09:09:14: 0047    200

10/05/2017 09:09:14: 0049    If 200 = 200 then

10/05/2017 09:09:14: 0050      CUSTOM CONNECT*Provider*QvBigQueryConnector.exe*XUserId*XPassword*

10/05/2017 09:09:19: 0051    ENDIF

10/05/2017 09:09:19: 0054    If 200 = 300 then

10/05/2017 09:09:19: 0058    LET vTableName = peek('TableName',0)

10/05/2017 09:09:19: 0059    trace store_pick.Payments

10/05/2017 09:09:19: 0059    store_pick.Payments

10/05/2017 09:09:19: 0060    LET vLoadType = peek('LoadType',0)

10/05/2017 09:09:19: 0061    trace 1

10/05/2017 09:09:19: 0061    1

10/05/2017 09:09:19: 0063    Let vMax ='store_pick.Payments'&'_Max'

10/05/2017 09:09:19: 0065    LET vStartDate = peek('StartDate',0)

10/05/2017 09:09:19: 0066    trace 20170427

10/05/2017 09:09:19: 0066    20170427

10/05/2017 09:09:19: 0067    LET vEndDate = peek('EndDate',0)

10/05/2017 09:09:19: 0068    trace 20170505

10/05/2017 09:09:19: 0068    20170505

10/05/2017 09:09:19: 0075    If 1 = 0 then

10/05/2017 09:09:19: 0087    If 1 = 1 then

10/05/2017 09:09:19: 0088      Load Max(maxLoad) as maxLoad

10/05/2017 09:09:19: 0089      from

10/05/2017 09:09:19: 0090      [store_pick.Payments_Max.qvd](qvd)

10/05/2017 09:09:19:           1 fields found: maxLoad, 1 lines fetched

10/05/2017 09:09:19: 0092      vStartDate= Date(Date#(Peek('maxLoad',0),'YYYYMMDD')+1,'YYYYMMDD')

10/05/2017 09:09:19: 0094      trace 20170509

10/05/2017 09:09:19: 0094      20170509

10/05/2017 09:09:19: 0095      vEndDate = 20170508

10/05/2017 09:09:19: 0096      trace 20170508

10/05/2017 09:09:19: 0096      20170508

10/05/2017 09:09:19: 0097    ENDIF

10/05/2017 09:09:19: 0099    If 1 = 2 then

10/05/2017 09:09:19: 0114    IF 200 = 200 then

10/05/2017 09:09:19: 0115      store_pick.Payments:

10/05/2017 09:09:19: 0116      SQL

10/05/2017 09:09:19: 0117     

10/05/2017 09:09:19: 0118       SELECT * , Integer(STRFTIME_UTC_USEC(createdDate,"%Y%m%d")) as loadDateKey FROM store_pick.Payments

10/05/2017 09:09:19: 0119       where Integer(STRFTIME_UTC_USEC(createdDate,"%Y%m%d")) >= 20170509

10/05/2017 09:09:19: 0120       and  Integer(STRFTIME_UTC_USEC(createdDate,"%Y%m%d")) <= 20170508

10/05/2017 09:09:21:           21 fields found: paymentId, orderId, customerId, retailerCustomerId, paymentStatusUpdatedDateTime, finalTotalAmount, paymentCurrency, fundingInstrument, paymentServiceProvider, paymentCompletionDateTime, transactionId, transactionType, transactionStatus, transactionCode, failureReason, createdDate, createdBy, fileName, partitionDt, version, loadDateKey, 0 lines fetched

10/05/2017 09:09:21: 0124      store_pick.Payments_Max:

10/05/2017 09:09:21: 0125     

10/05/2017 09:09:21: 0126      LOAD max(loadDateKey) as maxLoad

10/05/2017 09:09:21: 0127      Resident store_pick.Payments

10/05/2017 09:09:21:           1 fields found: maxLoad, 1 lines fetched

10/05/2017 09:09:21: 0128      STORE store_pick.Payments_Max into [store_pick.Payments_Max.qvd](qvd)

10/05/2017 09:09:21: 0129      DROP Table store_pick.Payments_Max

10/05/2017 09:09:21: 0133    ENDIF

10/05/2017 09:09:21: 0135    IF 200 = 300 then

10/05/2017 09:09:21: 0158    IF noOfRows('store_pick.Payments') <> 0 then

10/05/2017 09:09:21: 0211    DROP Table store_pick.Payments

10/05/2017 09:09:21: 0223  NEXT i

10/05/2017 09:09:21: 0046    Let vCon =  peek('Connection',1)

10/05/2017 09:09:21: 0047    trace 200

10/05/2017 09:09:21: 0047    200

10/05/2017 09:09:21: 0049    If 200 = 200 then

10/05/2017 09:09:21: 0050      CUSTOM CONNECT*Provider*QvBigQueryConnector.exe*XUserId*XPassword*

10/05/2017 09:09:25: 0051    ENDIF

10/05/2017 09:09:25: 0054    If 200 = 300 then

10/05/2017 09:09:25: 0058    LET vTableName = peek('TableName',1)

10/05/2017 09:09:25: 0059    trace store_pick.Refunds

10/05/2017 09:09:25: 0059    store_pick.Refunds

10/05/2017 09:09:25: 0060    LET vLoadType = peek('LoadType',1)

10/05/2017 09:09:25: 0061    trace 1

10/05/2017 09:09:25: 0061    1

10/05/2017 09:09:25: 0063    Let vMax ='store_pick.Refunds'&'_Max'

10/05/2017 09:09:25: 0065    LET vStartDate = peek('StartDate',1)

10/05/2017 09:09:25: 0066    trace 20170430

10/05/2017 09:09:25: 0066    20170430

10/05/2017 09:09:25: 0067    LET vEndDate = peek('EndDate',1)

10/05/2017 09:09:25: 0068    trace 20170505

10/05/2017 09:09:25: 0068    20170505

10/05/2017 09:09:25: 0075    If 1 = 0 then

10/05/2017 09:09:25: 0087    If 1 = 1 then

10/05/2017 09:09:25: 0088      Load Max(maxLoad) as maxLoad

10/05/2017 09:09:25: 0089      from

10/05/2017 09:09:25: 0090      [store_pick.Refunds_Max.qvd](qvd)

10/05/2017 09:09:25:           1 fields found: maxLoad, 1 lines fetched

10/05/2017 09:09:25: 0092      vStartDate= Date(Date#(Peek('maxLoad',0),'YYYYMMDD')+1,'YYYYMMDD')

10/05/2017 09:09:25: 0094      trace 20170509

10/05/2017 09:09:25: 0094      20170509

10/05/2017 09:09:25: 0095      vEndDate = 20170508

10/05/2017 09:09:25: 0096      trace 20170508

10/05/2017 09:09:25: 0096      20170508

10/05/2017 09:09:25: 0097    ENDIF

10/05/2017 09:09:25: 0099    If 1 = 2 then

10/05/2017 09:09:25: 0114    IF 200 = 200 then

10/05/2017 09:09:25: 0115      store_pick.Refunds:

10/05/2017 09:09:25: 0116      SQL

10/05/2017 09:09:25: 0117     

10/05/2017 09:09:25: 0118       SELECT * , Integer(STRFTIME_UTC_USEC(createdDate,"%Y%m%d")) as loadDateKey FROM store_pick.Refunds

10/05/2017 09:09:25: 0119       where Integer(STRFTIME_UTC_USEC(createdDate,"%Y%m%d")) >= 20170509

10/05/2017 09:09:25: 0120       and  Integer(STRFTIME_UTC_USEC(createdDate,"%Y%m%d")) <= 20170508

10/05/2017 09:09:28:           27 fields found: refundId, refundRequestSource, refundRequestDateTime, paymentId, orderId, customerId, retailerCustomerId, refundType, refundReason, refundAmountRequested, refundAmountAuthorized, refundAuthorizedDateTime, finalRefundTotalAmount, paymentCurrency, fundingInstrument, paymentServiceProvider, paymentCompletionDateTime, transactionId, transactionStatus, transactionCode, failureReason, createdDate, createdBy, fileName, partitionDt, version, loadDateKey, 0 lines fetched

10/05/2017 09:09:28: 0124      store_pick.Refunds_Max:

10/05/2017 09:09:28: 0125     

10/05/2017 09:09:28: 0126      LOAD max(loadDateKey) as maxLoad

10/05/2017 09:09:28: 0127      Resident store_pick.Refunds

10/05/2017 09:09:28:           1 fields found: maxLoad, 1 lines fetched

10/05/2017 09:09:28: 0128      STORE store_pick.Refunds_Max into [store_pick.Refunds_Max.qvd](qvd)

10/05/2017 09:09:28: 0129      DROP Table store_pick.Refunds_Max

10/05/2017 09:09:28: 0133    ENDIF

10/05/2017 09:09:28: 0135    IF 200 = 300 then

10/05/2017 09:09:28: 0158    IF noOfRows('store_pick.Refunds') <> 0 then

10/05/2017 09:09:28: 0211    DROP Table store_pick.Refunds

10/05/2017 09:09:28: 0223  NEXT i

10/05/2017 09:09:28: 0046    Let vCon =  peek('Connection',2)

10/05/2017 09:09:28: 0047    trace 300

10/05/2017 09:09:28: 0047    300

10/05/2017 09:09:28: 0049    If 300 = 200 then

10/05/2017 09:09:28: 0054    If 300 = 300 then

10/05/2017 09:09:28: 0055      OLEDB CONNECT*Provider*XPassword*

10/05/2017 09:09:28: 0056    ENDIF

10/05/2017 09:09:28: 0058    LET vTableName = peek('TableName',2)

10/05/2017 09:09:28: 0059    trace BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY

10/05/2017 09:09:28: 0059    BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY

10/05/2017 09:09:28: 0060    LET vLoadType = peek('LoadType',2)

10/05/2017 09:09:28: 0061    trace 2

10/05/2017 09:09:28: 0061    2

10/05/2017 09:09:28: 0063    Let vMax ='BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY'&'_Max'

10/05/2017 09:09:28: 0065    LET vStartDate = peek('StartDate',2)

10/05/2017 09:09:28: 0066    trace 20170427

10/05/2017 09:09:28: 0066    20170427

10/05/2017 09:09:28: 0067    LET vEndDate = peek('EndDate',2)

10/05/2017 09:09:28: 0068    trace 20170505

10/05/2017 09:09:28: 0068    20170505

10/05/2017 09:09:28: 0075    If 2 = 0 then

10/05/2017 09:09:28: 0087    If 2 = 1 then

10/05/2017 09:09:28: 0099    If 2 = 2 then

10/05/2017 09:09:28: 0106      LET vStartDate = peek('StartDate',2)

10/05/2017 09:09:28: 0107      trace 20170427

10/05/2017 09:09:28: 0107      20170427

10/05/2017 09:09:28: 0108      LET vEndDate = 20170508

10/05/2017 09:09:28: 0109      trace 20170508

10/05/2017 09:09:28: 0109      20170508

10/05/2017 09:09:28: 0111    ENDIF

10/05/2017 09:09:28: 0114    IF 300 = 200 then

10/05/2017 09:09:28: 0135    IF 300 = 300 then

10/05/2017 09:09:28: 0136      BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY:

10/05/2017 09:09:28: 0137      SQL

10/05/2017 09:09:28: 0138      SELECT * FROM BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY

10/05/2017 09:09:28: 0139      where DAY_KEY >= 20170427

10/05/2017 09:09:28: 0140      and DAY_KEY <= 20170508

10/05/2017 09:09:28: 0141      and org_bsns_unit_key = 1

10/05/2017 09:09:29:           14 fields found: DAY_KEY, ACCTING_DAY_KEY, SKU_ITEM_KEY, ORG_BSNS_UNIT_KEY, LFL_CTGRY_KEY, SCN_SLS_UNITS, NET_AMT, SLS_TRNVR_AMT, SRC_FLG, VNDR_NBR, LOAD_DT, LAST_UPDT_DT, LAST_UPDT_BY, SLS_UOM_UNITS, 133,637 lines fetched

10/05/2017 09:10:15: 0148      BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY_Max:

10/05/2017 09:10:15: 0149     

10/05/2017 09:10:15: 0150      LOAD max(DAY_KEY) as maxLoad

10/05/2017 09:10:15: 0151      Resident BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY

10/05/2017 09:10:19:           1 fields found: maxLoad, 1 lines fetched

10/05/2017 09:10:19: 0152      STORE BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY_Max into [BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY_Max.qvd](qvd)

10/05/2017 09:10:19: 0153      DROP Table BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY_Max

10/05/2017 09:10:19: 0154    ENDIF

10/05/2017 09:10:19: 0158    IF noOfRows('BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY') <> 0 then

10/05/2017 09:10:19: 0159      If 2 = 0 then

10/05/2017 09:10:19: 0163      If 2 = 1 then

10/05/2017 09:10:19: 0172      If 2 = 2 then

10/05/2017 09:10:19: 0173        Let vFromDate = Date(Date#(20170427,'YYYYMMDD'),'YYYYMMDD')

10/05/2017 09:10:19: 0174        TRACE ---20170427

10/05/2017 09:10:19: 0174        ---20170427

10/05/2017 09:10:19: 0176        Let vToDate = Date(Date#(20170508,'YYYYMMDD')- 1,'YYYYMMDD')

10/05/2017 09:10:19: 0177        Trace ---20170507

10/05/2017 09:10:19: 0177        ---20170507

10/05/2017 09:10:19: 0190        FOR j=vFromDate to vToDate

10/05/2017 09:10:19: 0191          Let vFromDate =  Date(Date#(20170427,'YYYYMMDD') + 1,'YYYYMMDD')

10/05/2017 09:10:19: 0192          TRACE -- 20170428

10/05/2017 09:10:19: 0192          -- 20170428

10/05/2017 09:10:19: 0193          Let Day_Table = 'BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY'&'_'&'20170428'

10/05/2017 09:10:19: 0194          BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY_20170428:

10/05/2017 09:10:19: 0195           SQL

10/05/2017 09:10:19: 0196                SELECT * FROM BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY

10/05/2017 09:10:19: 0197                where DAY_KEY = 20170428

10/05/2017 09:10:19: 0198                and org_bsns_unit_key = 1

10/05/2017 09:10:19:               14 fields found: DAY_KEY, ACCTING_DAY_KEY, SKU_ITEM_KEY, ORG_BSNS_UNIT_KEY, LFL_CTGRY_KEY, SCN_SLS_UNITS, NET_AMT, SLS_TRNVR_AMT, SRC_FLG, VNDR_NBR, LOAD_DT, LAST_UPDT_DT, LAST_UPDT_BY, SLS_UOM_UNITS, 145,843 lines fetched

10/05/2017 09:10:20: 0199          STORE [BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY_20170428] into [BIA_RTL.DWA_X_RTL_SL_ITEM_LOC_DAY_20170428.qvd] (qvd)

10/05/2017 09:10:20:               Error: Table not found

10/05/2017 09:12:16:               General Script Error

10/05/2017 09:12:16:               Execution Failed

10/05/2017 09:12:16:      Execution finished.

4 Replies

Re: Issue in while storing data into qvd

Hello,

looks like your table is concatenating with previous loaded table.

write noconcatenate before table name and try

Regards,

Regards,
Prashant Sangle
sunil1989
New Contributor

Re: Issue in while storing data into qvd

Thanks Prashant.

Script is behaving as you mentioned in above comment.

Highlighted

Re: Issue in while storing data into qvd

Ok.

Noconcatenate will resolve your issue. but create synthetic key.

are you still facing any issue???

Regards

Regards,
Prashant Sangle
sunil1989
New Contributor

Re: Issue in while storing data into qvd

No ,Thanks for the help.