Skip to main content
Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for 
Search instead for 
Did you mean: 
haneeshmarella
Creator II
Creator II

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

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

;SS.PNG

SS.PNG

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

16 Replies
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
Creator II
Creator II
Author

Hi stalwar1‌,

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

sunny_talwar

After adding NoConcatenate?

haneeshmarella
Creator II
Creator II
Author

Yes, after adding.

sunny_talwar

What was the error message though?

swuehl
MVP
MVP

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
Creator II
Creator II
Author

SS.PNG

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

haneeshmarella
Creator II
Creator II
Author

Hi Stefan,

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

swuehl
MVP
MVP

Could you also temporarily comment the STORE statement and retry?