4 Replies Latest reply: May 12, 2017 2:26 AM by Sunilkumar Yadav RSS

    Issue in while storing data into qvd

    Sunilkumar Yadav

      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.