Incremental Load -Qlik sense

[Lavanya11]

I am new to qlik sense and trying to implement incremental load .

Created a QVD  : ITEM_DIM2.qvd and tried to use below code to implement incremental load  and it doesn't seem to work .I tried to do the same with other table too but it didn't work .Can someone help me find the issue .Thanks !

Last_Updated_Date:
load max(DATE_POSTED) as MaxDate
from [lib://QVD_Connection/ITEM_DIM2.qvd] (qvd);

Let Last_Updated_Date = peek('MaxDate','0','Last_Updated_Date');

[items]:
Load "DATE_POSTED",
"STRIPPED_STOCK",
"DESC_",
"MAN_CD",
"MAN_NAME",
"VEND_CD",
"VEND_NAME",
"PROD_STATUS",
"PROD_STATUS_DESC",
"CAT_CD",
"CAT_DESC",
"MASTER_CAT_CD",
"MASTER_CAT_DESC",
"MGR_INITIALS",
"CATALOG_PG_NUM",
"LIST_PRICE",
"MATRIXSA_PRICE",
"MATRIXMP_PRICE",
"ALWAYS_NET",
"DATED_GOODS",
"UPS_ABLE",
"PROD_CNTRY_MANU",
"QUICK_SHIP",
"RTA_FLAG",
"SPECIAL_ORDER",
"EXTRA_VAL_RATE",
"INVTRY_TURNS",
"RDC_ITEM",
"VIRTUAL_SKU",
"STOCKED_SKU",
"UOM_MULTIPLE",
"PL1_QTY",
"PL1_UOM1",
"PL1_UOM2",
"PL2_QTY",
"PL2_UOM1",
"PL2_UOM2",
"PL3_QTY",
"PL3_UOM1",
"PL3_UOM2",
"PL4_QTY",
"PL4_UOM1",
"PL4_UOM2",
"SALESOBJ_ID",
"SPR_UOM",
"PROD_MIX_DESC",
"IN_GENERAL_LINE",
"BRANCH_ONLY",
"SPR_PRIV_LBL",
"DLR_PRIV_LBL",
"LOAD_DT_TM",
"HOLD_COST_PCT",
"Z_SEND_FLG",
"SPECIAL_PRODUCT",
"OLD_STRIPPED_STOCK",
"HRZ_ITEM_GROUP",
"HRZ_PROD_CLASS",
"HRZ_PROD_SUBCLASS",
"OLD_UOM",
"CONV_FACTOR",
"MKT_ENTITY",
"DEMANDOBJ_ID",
"PROGRAM_TYPE",
"VEND_MIN_ORD_QTY",
"NEW_9MO_ITM",
"STATUS_CHANGE_DATE",
"DOMESTIC_VENDOR",
"WEIGHT" ;
SELECT
"DATE_POSTED",
"STRIPPED_STOCK",
"DESC_",
"MAN_CD",
"MAN_NAME",
"VEND_CD",
"VEND_NAME",
"PROD_STATUS",
"PROD_STATUS_DESC",
"CAT_CD",
"CAT_DESC",
"MASTER_CAT_CD",
"MASTER_CAT_DESC",
"MGR_INITIALS",
"CATALOG_PG_NUM",
"LIST_PRICE",
"MATRIXSA_PRICE",
"MATRIXMP_PRICE",
"ALWAYS_NET",
"DATED_GOODS",
"UPS_ABLE",
"PROD_CNTRY_MANU",
"QUICK_SHIP",
"RTA_FLAG",
"SPECIAL_ORDER",
"EXTRA_VAL_RATE",
"INVTRY_TURNS",
"RDC_ITEM",
"VIRTUAL_SKU",
"STOCKED_SKU",
"UOM_MULTIPLE",
"PL1_QTY",
"PL1_UOM1",
"PL1_UOM2",
"PL2_QTY",
"PL2_UOM1",
"PL2_UOM2",
"PL3_QTY",
"PL3_UOM1",
"PL3_UOM2",
"PL4_QTY",
"PL4_UOM1",
"PL4_UOM2",
"SALESOBJ_ID",
"SPR_UOM",
"PROD_MIX_DESC",
"IN_GENERAL_LINE",
"BRANCH_ONLY",
"SPR_PRIV_LBL",
"DLR_PRIV_LBL",
"LOAD_DT_TM",
"HOLD_COST_PCT",
"Z_SEND_FLG",
"SPECIAL_PRODUCT",
"OLD_STRIPPED_STOCK",
"HRZ_ITEM_GROUP",
"HRZ_PROD_CLASS",
"HRZ_PROD_SUBCLASS",
"OLD_UOM",
"CONV_FACTOR",
"MKT_ENTITY",
"DEMANDOBJ_ID",
"PROGRAM_TYPE",
"VEND_MIN_ORD_QTY",
"NEW_9MO_ITM",
"STATUS_CHANGE_DATE",
"DOMESTIC_VENDOR",
"WEIGHT" FROM "DW"."ITEM_DIM2" where DATE_POSTED > $(Last_Updated_Date);

CONCATENATE
LOAD
*
FROM [lib://QVD_Connection/ITEM_DIM2.qvd] (qvd);

STORE [items] into [lib://QVD_Connection/ITEM_DIM2.qvd] (qvd);

[Anil_Babu_Samineni]

For me this is correct, What is wrong? And which statement DML you are trying? 

Did you missed single Quote to the variable?

[Lavanya11]

When I preview the data for Last_Update_Date .I see a number instead of DATE .

I get  this error when I try to load   [items]:

The following error occurred:

Field 'DATE_POSTED' not found

The error occurred here:

?

[JordyWegman]

Hi Lavanyapati,

You are probably getting an error because of the '>' in your where statement. Change this to '>=' because it possible that there is no greater date in your database.

Jordy

Climber

[Anil_Babu_Samineni]

There are other ways can be done? Before that, Can you just remove "DATE_POSTED" which is repeated 2 times in the load?

[Lavanya11]

I am pretty sure there is data . Even though I tried your suggestion but it didn't work  😞

[Lavanya11]

 I tried make changes as  you suggested by taking away the LOAD script and just starting it from SQL SELECT  and I get this error . Should I be changing the date format ?

updated incremental load logic :

Last_Updated_Date:
load DATE(max(DATE_POSTED)) as MaxDate
from [lib://QVD_Connection /ITEM_DIM2.qvd] (qvd);

Let MaxDate = peek('MaxDate',0,Last_Updated_Date);

[items]:

SQL SELECT
  "DATE_POSTED",
 "STRIPPED_STOCK",
 "DESC_",
 "MAN_CD",
 "MAN_NAME",
 "VEND_CD",
 "VEND_NAME",
 "PROD_STATUS",
 "PROD_STATUS_DESC",
 "CAT_CD",
 "CAT_DESC",
 "MASTER_CAT_CD",
 "MASTER_CAT_DESC",
 "MGR_INITIALS",
 "CATALOG_PG_NUM",
 "LIST_PRICE",
 "MATRIXSA_PRICE",
 "MATRIXMP_PRICE",
 "ALWAYS_NET",
 "DATED_GOODS",
 "UPS_ABLE",
 "PROD_CNTRY_MANU",
 "QUICK_SHIP",
 "RTA_FLAG",
 "SPECIAL_ORDER",
 "EXTRA_VAL_RATE",
 "INVTRY_TURNS",
 "RDC_ITEM",
 "VIRTUAL_SKU",
 "STOCKED_SKU",
 "UOM_MULTIPLE",
 "PL1_QTY",
 "PL1_UOM1",
 "PL1_UOM2",
 "PL2_QTY",
 "PL2_UOM1",
 "PL2_UOM2",
 "PL3_QTY",
 "PL3_UOM1",
 "PL3_UOM2",
 "PL4_QTY",
 "PL4_UOM1",
 "PL4_UOM2",
 "SALESOBJ_ID",
 "SPR_UOM",
 "PROD_MIX_DESC",
 "IN_GENERAL_LINE",
 "BRANCH_ONLY",
 "SPR_PRIV_LBL",
 "DLR_PRIV_LBL",
 "LOAD_DT_TM",
 "HOLD_COST_PCT",
 "Z_SEND_FLG",
 "SPECIAL_PRODUCT",
 "OLD_STRIPPED_STOCK",
 "HRZ_ITEM_GROUP",
 "HRZ_PROD_CLASS",
 "HRZ_PROD_SUBCLASS",
 "OLD_UOM",
 "CONV_FACTOR",
 "MKT_ENTITY",
 "DEMANDOBJ_ID",
 "PROGRAM_TYPE",
 "VEND_MIN_ORD_QTY",
 "NEW_9MO_ITM",
 "STATUS_CHANGE_DATE",
 "DOMESTIC_VENDOR",
 "WEIGHT"  FROM "DW"."ITEM_DIM2" where DATE_POSTED >= '$(MaxDate)';

CONCATENATE
LOAD
    *
 FROM [lib://QVD_Connection /ITEM_DIM2.qvd] (qvd);

STORE [items] into [lib://QVD_Connection /ITEM_DIM2.qvd] (qvd);

 exit script;

Error Message :

The following error occurred:

QVX_UNEXPECTED_END_OF_DATA: ERROR [HY000] ORA-01843: not a valid month

The error occurred here:

[items]:


SQL SELECT
  "DATE_POSTED",
"STRIPPED_STOCK",
"DESC_",
"MAN_CD",
"MAN_NAME",
"VEND_CD",
"VEND_NAME",
"PROD_STATUS",
"PROD_STATUS_DESC",
"CAT_CD",
"CAT_DESC",
"MASTER_CAT_CD",
"MASTER_CAT_DESC",
"MGR_INITIALS",
"CATALOG_PG_NUM",
"LIST_PRICE",
"MATRIXSA_PRICE",
"MATRIXMP_PRICE",
"ALWAYS_NET",
"DATED_GOODS",
"UPS_ABLE",
"PROD_CNTRY_MANU",
"QUICK_SHIP",
"RTA_FLAG",
"SPECIAL_ORDER",
"EXTRA_VAL_RATE",
"INVTRY_TURNS",
"RDC_ITEM",
"VIRTUAL_SKU",
"STOCKED_SKU",
"UOM_MULTIPLE",
"PL1_QTY",
"PL1_UOM1",
"PL1_UOM2",
"PL2_QTY",
"PL2_UOM1",
"PL2_UOM2",
"PL3_QTY",
"PL3_UOM1",
"PL3_UOM2",
"PL4_QTY",
"PL4_UOM1",
"PL4_UOM2",
"SALESOBJ_ID",
"SPR_UOM",
"PROD_MIX_DESC",
"IN_GENERAL_LINE",
"BRANCH_ONLY",
"SPR_PRIV_LBL",
"DLR_PRIV_LBL",
"LOAD_DT_TM",
"HOLD_COST_PCT",
"Z_SEND_FLG",
"SPECIAL_PRODUCT",
"OLD_STRIPPED_STOCK",
"HRZ_ITEM_GROUP",
"HRZ_PROD_CLASS",
"HRZ_PROD_SUBCLASS",
"OLD_UOM",
"CONV_FACTOR",
"MKT_ENTITY",
"DEMANDOBJ_ID",
"PROGRAM_TYPE",
"VEND_MIN_ORD_QTY",
"NEW_9MO_ITM",
"STATUS_CHANGE_DATE",
"DOMESTIC_VENDOR",
"WEIGHT"  FROM "DW"."ITEM_DIM2" where DATE_POSTED >= '4/26/2019'

[vikramv · Accepted solution]

I think the issue is in field formatting in the condition.

DATE_POSTED and Last_Updated_Date 

Try to convert both to number and check the condition something like Num(DATE_POSTED) > NUM($(Last_Updated_Date))

or DATE#(DATE_POSTED) > DATE#($(Last_Updated_Date))

[Lavanya11]

Date conversion was the problem .I got it working now .Thank You!

[Lavanya11]

Thanks for you help ! It's working now .Can you suggest me other ways to do data load in incremental fashion .