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

;

[haneeshmarella]

Yes, it does work without the STORE statement.

[vishsaggi]

May be check here? Your table name is incorrect in Peek.

//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')

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

[haneeshmarella]

I get 'Execution of script failed, Load old data?' window when I made the change and used the STORE statement.

[swuehl]

If it does work without the STORE statement, I assume that the QVD is stilled locked by another process. Try closing all QV instances (check also the task manager) and retry.

[haneeshmarella]

Works now! I closed everything, and reopened, and the STORE statement works.

Thank you Stefan.

Can you help me out here if you have any ideas on this too please? How do I create Auto-Timer in Straight Table?

Thanks a lot again

[vishsaggi]

Ok as you got the Store issues resolved. Do check your variable if it holds any date value? Previously in peek it was pointing to a table name which was not right, hence, replied in my earlier response. So worth checking the variable value because you are using this variable in your SQL statement...

[haneeshmarella]

Hi Nagaraju, Yes, I did change the table name as you have mentioned. Thank you for that, I would have overseen it if you didn't mention.