Error in STORE and DROP of Incremental Load. What am I doing wrong?

[haneeshmarella]

Hi guys, I get the below errors when I try Incremental load. Please help.

Final_Table:

LOAD

PICKTICKET#,

SHIP_TO_COUNTRY,

PLANNED_SHIP_VIA,

TOTAL_NO_OF_CARTONS,

PKMS_USERS,

CASE#,

TO_LOCATION,

MENU_OPTION_NAME,

FROM_LOCATION,

STYLE,

UNITS#,

DATE_CREATED,

TIME_CREATED,

USER_NAME,

timestamp(TimeStamp(floor(TimeStamp#(DATE_CREATED & IF 

( LEN(TIME_CREATED) = 1 , '00000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =2  , '0000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =3  , '000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =4  , '00' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =5  , '0' & TIME_CREATED,

TIME_CREATED )

)))),'YYYYMMDDhhmmss' ),1/24),'MM/DD/YYYY - hh TT')-1/24,'MM/DD/YYYY - hh TT') as DATE_TIME_CST

FROM "C:\Users\marellah\Desktop\QV Development\QlikView Dashboards\MOD3_Data_Source.QVD" (QVD)

;

//Find Last Modified Date

Max_Date:

LOAD max(DATE_CREATED) as MaxDate

Resident Final_Table

;

//Store Last Modified Date to a Variable

Let Last_Updated_Date = Peek('MaxDate',0,'Last_Updated_Date')

;

DROP Table Max_Date;

//Load Incremental Data

Incremental_Load:

LOAD

PICKTICKET#,

SHIP_TO_COUNTRY,

PLANNED_SHIP_VIA,

TOTAL_NO_OF_CARTONS,

PKMS_USERS,

CASE#,

TO_LOCATION,

MENU_OPTION_NAME,

FROM_LOCATION,

STYLE,

UNITS#,

DATE_CREATED,

TIME_CREATED,

USER_NAME,

timestamp(TimeStamp(floor(TimeStamp#(DATE_CREATED & IF 

( LEN(TIME_CREATED) = 1 , '00000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =2  , '0000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =3  , '000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =4  , '00' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =5  , '0' & TIME_CREATED,

TIME_CREATED )

)))),'YYYYMMDDhhmmss' ),1/24),'MM/DD/YYYY - hh TT')-1/24,'MM/DD/YYYY - hh TT') as DATE_TIME_CST;

SQL SELECT DISTINCT A.PHPKTN AS PICKTICKET#,

    A.PHSHCN AS SHIP_TO_COUNTRY,

    A.PHSVIA AS PLANNED_SHIP_VIA,

    A.PHTCT AS TOTAL_NO_OF_CARTONS,

    B.PRUSER AS PKMS_USERS,

    B.PRCASN AS CASE#,

    B.PRTLOC AS TO_LOCATION,

    B.PRMNOP AS MENU_OPTION_NAME,

    B.PRFLOC AS FROM_LOCATION,

    B.PRSTYL AS STYLE,

    B.PRUNTS AS UNITS#,

    B.PRDCR AS DATE_CREATED,

    B.PRTCR AS TIME_CREATED,

    C.USER_NAME AS USER_NAME

FROM CAPM01.WM0272PRDD.PHPICK00 A, CAPM01.WM0272PRDD.PRTRAN00 B, CAPM01.WM0272PRDD.USUSER00 C

WHERE A.PHPCTL=B.PRPCTL

AND B.PRDCR > '$(Last_Updated_Date)'

AND B.PRUSER=C.USER_ID

AND B.PRTXTP='500'

AND B.PRTXCD='002'

AND B.PRWHSE='FG3'

AND B.PRDIV='08'

//AND TO_DATE(B.PRDCR,'YYYYMMDD')=CURRENT_DATE

//AND B.PRDCR = '$(Pickdate)'

//AND B.PRDCR > '20180625'

AND B.PRMNOP='Pick/Pack'

AND B.PRCASN IS NOT NULL

AND B.PRFLOC LIKE '%M3%'

;

//Concatenate with QVD

Concatenate

LOAD

PICKTICKET#,

SHIP_TO_COUNTRY,

PLANNED_SHIP_VIA,

TOTAL_NO_OF_CARTONS,

PKMS_USERS,

CASE#,

TO_LOCATION,

MENU_OPTION_NAME,

FROM_LOCATION,

STYLE,

UNITS#,

DATE_CREATED,

TIME_CREATED,

USER_NAME,

timestamp(TimeStamp(floor(TimeStamp#(DATE_CREATED & IF 

( LEN(TIME_CREATED) = 1 , '00000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =2  , '0000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =3  , '000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =4  , '00' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =5  , '0' & TIME_CREATED,

TIME_CREATED )

)))),'YYYYMMDDhhmmss' ),1/24),'MM/DD/YYYY - hh TT')-1/24,'MM/DD/YYYY - hh TT') as DATE_TIME_CST

FROM "C:\Users\marellah\Desktop\QV Development\QlikView Dashboards\MOD3_Data_Source.QVD" (QVD)

;

//Replace Old QVD

STORE Incremental_Load into "C:\Users\marellah\Desktop\QV Development\QlikView Dashboards\MOD3_Data_Source.QVD" (QVD)

;

//Drop New Table

DROP Table Incremental_Load

;

[sunny_talwar]

May be try this

Final_Table:

LOAD

PICKTICKET#,

SHIP_TO_COUNTRY,

PLANNED_SHIP_VIA,

TOTAL_NO_OF_CARTONS,

PKMS_USERS,

CASE#,

TO_LOCATION,

MENU_OPTION_NAME,

FROM_LOCATION,

STYLE,

UNITS#,

DATE_CREATED,

TIME_CREATED,

USER_NAME,

timestamp(TimeStamp(floor(TimeStamp#(DATE_CREATED & IF

( LEN(TIME_CREATED) = 1 , '00000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =2  , '0000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =3  , '000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =4  , '00' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =5  , '0' & TIME_CREATED,

TIME_CREATED )

)))),'YYYYMMDDhhmmss' ),1/24),'MM/DD/YYYY - hh TT')-1/24,'MM/DD/YYYY - hh TT') as DATE_TIME_CST

FROM "C:\Users\marellah\Desktop\QV Development\QlikView Dashboards\MOD3_Data_Source.QVD" (QVD)

;

//Find Last Modified Date

Max_Date:

LOAD max(DATE_CREATED) as MaxDate

Resident Final_Table

;

//Store Last Modified Date to a Variable

Let Last_Updated_Date = Peek('MaxDate',0,'Last_Updated_Date')

;

DROP Table Max_Date;

//Load Incremental Data

Incremental_Load:

NoConcatenate

LOAD

PICKTICKET#,

SHIP_TO_COUNTRY,

PLANNED_SHIP_VIA,

TOTAL_NO_OF_CARTONS,

PKMS_USERS,

CASE#,

TO_LOCATION,

MENU_OPTION_NAME,

FROM_LOCATION,

STYLE,

UNITS#,

DATE_CREATED,

TIME_CREATED,

USER_NAME,

timestamp(TimeStamp(floor(TimeStamp#(DATE_CREATED & IF

( LEN(TIME_CREATED) = 1 , '00000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =2  , '0000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =3  , '000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =4  , '00' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =5  , '0' & TIME_CREATED,

TIME_CREATED )

)))),'YYYYMMDDhhmmss' ),1/24),'MM/DD/YYYY - hh TT')-1/24,'MM/DD/YYYY - hh TT') as DATE_TIME_CST;

SQL SELECT DISTINCT A.PHPKTN AS PICKTICKET#,

    A.PHSHCN AS SHIP_TO_COUNTRY,

    A.PHSVIA AS PLANNED_SHIP_VIA,

    A.PHTCT AS TOTAL_NO_OF_CARTONS,

    B.PRUSER AS PKMS_USERS,

    B.PRCASN AS CASE#,

    B.PRTLOC AS TO_LOCATION,

    B.PRMNOP AS MENU_OPTION_NAME,

    B.PRFLOC AS FROM_LOCATION,

    B.PRSTYL AS STYLE,

    B.PRUNTS AS UNITS#,

    B.PRDCR AS DATE_CREATED,

    B.PRTCR AS TIME_CREATED,

    C.USER_NAME AS USER_NAME

FROM CAPM01.WM0272PRDD.PHPICK00 A, CAPM01.WM0272PRDD.PRTRAN00 B, CAPM01.WM0272PRDD.USUSER00 C

WHERE A.PHPCTL=B.PRPCTL

AND B.PRDCR > '$(Last_Updated_Date)'

AND B.PRUSER=C.USER_ID

AND B.PRTXTP='500'

AND B.PRTXCD='002'

AND B.PRWHSE='FG3'

AND B.PRDIV='08'

//AND TO_DATE(B.PRDCR,'YYYYMMDD')=CURRENT_DATE

//AND B.PRDCR = '$(Pickdate)'

//AND B.PRDCR > '20180625'

AND B.PRMNOP='Pick/Pack'

AND B.PRCASN IS NOT NULL

AND B.PRFLOC LIKE '%M3%'

;

//Concatenate with QVD

Concatenate

LOAD

PICKTICKET#,

SHIP_TO_COUNTRY,

PLANNED_SHIP_VIA,

TOTAL_NO_OF_CARTONS,

PKMS_USERS,

CASE#,

TO_LOCATION,

MENU_OPTION_NAME,

FROM_LOCATION,

STYLE,

UNITS#,

DATE_CREATED,

TIME_CREATED,

USER_NAME,

timestamp(TimeStamp(floor(TimeStamp#(DATE_CREATED & IF

( LEN(TIME_CREATED) = 1 , '00000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =2  , '0000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =3  , '000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =4  , '00' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =5  , '0' & TIME_CREATED,

TIME_CREATED )

)))),'YYYYMMDDhhmmss' ),1/24),'MM/DD/YYYY - hh TT')-1/24,'MM/DD/YYYY - hh TT') as DATE_TIME_CST

FROM "C:\Users\marellah\Desktop\QV Development\QlikView Dashboards\MOD3_Data_Source.QVD" (QVD)

;

//Replace Old QVD

STORE Incremental_Load into "C:\Users\marellah\Desktop\QV Development\QlikView Dashboards\MOD3_Data_Source.QVD" (QVD)

;

//Drop New Table

DROP Table Incremental_Load

[haneeshmarella]

Hi stalwar1‌,

It says 'Execution of script failed. Reload old data?'.

[sunny_talwar]

After adding NoConcatenate?

[haneeshmarella]

Yes, after adding.

[sunny_talwar]

What was the error message though?

[swuehl · Accepted solution]

Seems your Incremental_Load table gets auto-concatenated to the first table Final_Table.

Since you seem to only trying to add new records, you don't need the second QVD LOAD and you can omit the last DROP table if you want to keep Final_Table with the concatenated new records:

//Concatenate with QVD

Concatenate

LOAD

PICKTICKET#,

SHIP_TO_COUNTRY,

PLANNED_SHIP_VIA,

TOTAL_NO_OF_CARTONS,

PKMS_USERS,

CASE#,

TO_LOCATION,

MENU_OPTION_NAME,

FROM_LOCATION,

STYLE,

UNITS#,

DATE_CREATED,

TIME_CREATED,

USER_NAME,

timestamp(TimeStamp(floor(TimeStamp#(DATE_CREATED & IF

( LEN(TIME_CREATED) = 1 , '00000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =2  , '0000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =3  , '000' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =4  , '00' & TIME_CREATED,

IF  ( LEN(TIME_CREATED) =5  , '0' & TIME_CREATED,

TIME_CREATED )

)))),'YYYYMMDDhhmmss' ),1/24),'MM/DD/YYYY - hh TT')-1/24,'MM/DD/YYYY - hh TT') as DATE_TIME_CST

FROM "C:\Users\marellah\Desktop\QV Development\QlikView Dashboards\MOD3_Data_Source.QVD" (QVD)

;

//Replace Old QVD

STORE Final_Table into "C:\Users\marellah\Desktop\QV Development\QlikView Dashboards\MOD3_Data_Source.QVD" (QVD)

;

//Drop New Table

DROP Table Incremental_Load;

[haneeshmarella]

This is the error. The original error screenshots are in the main question at the bottom of it.

[haneeshmarella]

Hi Stefan,

I tried doing what you have suggested above, and get the below problem.

[swuehl]

Could you also temporarily comment the STORE statement and retry?