topic Re: Error in STORE and DROP of Incremental Load. What am I doing wrong? in QlikView

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

haneeshmarella — Wed, 25 Nov 2020 16:16:04 GMT

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

;

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

sunny_talwar — Mon, 16 Jul 2018 15:24:25 GMT

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

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

haneeshmarella — Mon, 16 Jul 2018 15:30:10 GMT

Hi stalwar1‌,

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

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

sunny_talwar — Mon, 16 Jul 2018 15:31:50 GMT

After adding NoConcatenate?

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

haneeshmarella — Mon, 16 Jul 2018 15:33:27 GMT

Yes, after adding.

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

sunny_talwar — Mon, 16 Jul 2018 15:37:18 GMT

What was the error message though?

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

swuehl — Mon, 16 Jul 2018 15:40:57 GMT

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;

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

haneeshmarella — Mon, 16 Jul 2018 15:48:48 GMT

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

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

haneeshmarella — Mon, 16 Jul 2018 16:07:11 GMT

Hi Stefan,

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

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

swuehl — Mon, 16 Jul 2018 16:11:43 GMT

Could you also temporarily comment the STORE statement and retry?

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

haneeshmarella — Mon, 16 Jul 2018 16:15:28 GMT

Yes, it does work without the STORE statement.

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

vishsaggi — Mon, 16 Jul 2018 16:18:57 GMT

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

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

haneeshmarella — Mon, 16 Jul 2018 16:21:35 GMT

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

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

swuehl — Mon, 16 Jul 2018 16:37:45 GMT

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.

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

haneeshmarella — Mon, 16 Jul 2018 16:42:02 GMT

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

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

vishsaggi — Mon, 16 Jul 2018 17:32:25 GMT

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...

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

haneeshmarella — Mon, 16 Jul 2018 18:19:07 GMT

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.