Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
;
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;
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
Hi stalwar1,
It says 'Execution of script failed. Reload old data?'.
After adding NoConcatenate?
Yes, after adding.
What was the error message though?
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;
This is the error. The original error screenshots are in the main question at the bottom of it.
Hi Stefan,
I tried doing what you have suggested above, and get the below problem.
Could you also temporarily comment the STORE statement and retry?