Dear experts,
I need to conduct incremental load with several steps:
1. create a application and load the data where CALDAY = 2021/05/21 into it and save it in a QVD file.
2. create another application and set a variable as vDAY = Date(Today()-1,'YYYYMMDD') and load the data everyday and push the new data into the QVD in step one so that the QVD has the data of each day. The scripting is as below:
QVD file application:
[inventory_v1]:
LOAD
"CALDAY",
left(CALDAY,4) as [年],
mid(CALDAY,5,2) as [月],
Right(CALDAY,2) as [日],
"ZMATERIAL" as 物料编码,
"ZMATERIAL_T" as 物料名称,
"ZCOMPCODE" as 公司代码,
"ZCOMPCODE___T" as 公司名称,
"ZPLANT" as 工厂代码,
"ZPLANT___T" as 工厂名称,
"ZSTRO_LOC" as 库位代码,
"ZSTRO_LOC___T" as 库位名称,
"ZLOC_CURRCY" as 货币代码,
"ZLOC_CURRCY___T" as 货币名称,
"ZMATL_TYPE" as 物料类型,
"ZVAL_CLASS" as 评估类,
"ZCC_M_TOTALSTK" as 当期库存数量,
"ZCC_M_STKAGE_1" as 一个月以内库存数量,
"ZCC_M_STKAGE_2" as 一到两个月库存数量,
"ZCC_M_STKAGE_3" as 两到三个月库存数量,
"ZCC_M_STKAGE_4" as 三到六个月库存数量,
"ZCC_M_STKAGE_5" as 六到九个月库存数量,
"ZCC_M_STKAGE_6" as 九到十一个月库存数量,
"ZCC_M_STKAGE_7" as 十一到十二个月库存数量,
"ZCC_M_STKAGE_8" as 一到两年库存数量,
"ZCC_M_STKAGE_9" as 两年以上库存数量,
"ZCC_M_VALAGE" as 当期库存金额,
"ZCC_M_VALAGE_1" as 一个月内库存金额,
"ZCC_M_VALAGE_2" as 一到两个月库存金额,
"ZCC_M_VALAGE_3" as 两到三个月库存金额,
"ZCC_M_VALAGE_4" as 三到六个月库存金额,
"ZCC_M_VALAGE_5" as 六到九个月库存金额,
"ZCC_M_VALAGE_6" as 九到十一个月库存金额,
"ZCC_M_VALAGE_7" as 十一到十二个月库存金额,
"ZCC_M_VALAGE_8" as 一到两年库存金额,
"ZCC_M_VALAGE_9" as 两年以上库存金额,
"ZCC_M_INACTSTK_QC" as 年初呆滞库存数量,
"ZCC_M_INACTVAL_QC" as 年初呆滞库存金额,
"ZCC_M_INACTSTK_NOW" as 现在呆滞库存数量,
"ZCC_M_INACTVAL_NOW" as 现在呆滞库存金额,
"ZCC_M_INACTVAL_RATE" as 当前与年初数差异率,
"ZBASE_UOM___T" as 基本单位名称,
"ZBASE_UOM" as 基本单位编码;
SQL SELECT "CALDAY",
"ZMATERIAL",
"ZMATERIAL_T",
"ZCOMPCODE",
"ZCOMPCODE___T",
"ZPLANT",
"ZPLANT___T",
"ZSTRO_LOC",
"ZSTRO_LOC___T",
"ZLOC_CURRCY",
"ZLOC_CURRCY___T",
"ZMATL_TYPE",
"ZVAL_CLASS",
"ZCC_M_TOTALSTK",
"ZCC_M_STKAGE_1",
"ZCC_M_STKAGE_2",
"ZCC_M_STKAGE_3",
"ZCC_M_STKAGE_4",
"ZCC_M_STKAGE_5",
"ZCC_M_STKAGE_6",
"ZCC_M_STKAGE_7",
"ZCC_M_STKAGE_8",
"ZCC_M_STKAGE_9",
"ZCC_M_VALAGE",
"ZCC_M_VALAGE_1",
"ZCC_M_VALAGE_2",
"ZCC_M_VALAGE_3",
"ZCC_M_VALAGE_4",
"ZCC_M_VALAGE_5",
"ZCC_M_VALAGE_6",
"ZCC_M_VALAGE_7",
"ZCC_M_VALAGE_8",
"ZCC_M_VALAGE_9",
"ZCC_M_INACTSTK_QC",
"ZCC_M_INACTVAL_QC",
"ZCC_M_INACTSTK_NOW",
"ZCC_M_INACTVAL_NOW",
"ZCC_M_INACTVAL_RATE",
"ZBASE_UOM",
"ZBASE_UOM___T"
FROM "_SYS_BIC"."WLBI2.AL.MM/CVC_MM_STOCKAGE"
('PLACEHOLDER' = ('$$IN_QUERYDATE$$', '20210520'));
Store inventory_v1 into [lib://QVD (qlik-pro_aria)/inventory_v1.qvd](qvd);
the second application:
[inventory_v2]:
LOAD
"CALDAY",
left(CALDAY,4) as [年],
mid(CALDAY,5,2) as [月],
Right(CALDAY,2) as [日],
"ZMATERIAL" as 物料编码,
"ZMATERIAL_T" as 物料名称,
"ZCOMPCODE" as 公司代码,
"ZCOMPCODE___T" as 公司名称,
"ZPLANT" as 工厂代码,
"ZPLANT___T" as 工厂名称,
"ZSTRO_LOC" as 库位代码,
"ZSTRO_LOC___T" as 库位名称,
"ZLOC_CURRCY" as 货币代码,
"ZLOC_CURRCY___T" as 货币名称,
"ZMATL_TYPE" as 物料类型,
"ZVAL_CLASS" as 评估类,
"ZCC_M_TOTALSTK" as 当期库存数量,
"ZCC_M_STKAGE_1" as 一个月以内库存数量,
"ZCC_M_STKAGE_2" as 一到两个月库存数量,
"ZCC_M_STKAGE_3" as 两到三个月库存数量,
"ZCC_M_STKAGE_4" as 三到六个月库存数量,
"ZCC_M_STKAGE_5" as 六到九个月库存数量,
"ZCC_M_STKAGE_6" as 九到十一个月库存数量,
"ZCC_M_STKAGE_7" as 十一到十二个月库存数量,
"ZCC_M_STKAGE_8" as 一到两年库存数量,
"ZCC_M_STKAGE_9" as 两年以上库存数量,
"ZCC_M_VALAGE" as 当期库存金额,
"ZCC_M_VALAGE_1" as 一个月内库存金额,
"ZCC_M_VALAGE_2" as 一到两个月库存金额,
"ZCC_M_VALAGE_3" as 两到三个月库存金额,
"ZCC_M_VALAGE_4" as 三到六个月库存金额,
"ZCC_M_VALAGE_5" as 六到九个月库存金额,
"ZCC_M_VALAGE_6" as 九到十一个月库存金额,
"ZCC_M_VALAGE_7" as 十一到十二个月库存金额,
"ZCC_M_VALAGE_8" as 一到两年库存金额,
"ZCC_M_VALAGE_9" as 两年以上库存金额,
"ZCC_M_INACTSTK_QC" as 年初呆滞库存数量,
"ZCC_M_INACTVAL_QC" as 年初呆滞库存金额,
"ZCC_M_INACTSTK_NOW" as 现在呆滞库存数量,
"ZCC_M_INACTVAL_NOW" as 现在呆滞库存金额,
"ZCC_M_INACTVAL_RATE" as 当前与年初数差异率,
"ZBASE_UOM___T" as 基本单位名称,
"ZBASE_UOM" as 基本单位编码;
SQL SELECT "CALDAY",
"ZMATERIAL",
"ZMATERIAL_T",
"ZCOMPCODE",
"ZCOMPCODE___T",
"ZPLANT",
"ZPLANT___T",
"ZSTRO_LOC",
"ZSTRO_LOC___T",
"ZLOC_CURRCY",
"ZLOC_CURRCY___T",
"ZMATL_TYPE",
"ZVAL_CLASS",
"ZCC_M_TOTALSTK",
"ZCC_M_STKAGE_1",
"ZCC_M_STKAGE_2",
"ZCC_M_STKAGE_3",
"ZCC_M_STKAGE_4",
"ZCC_M_STKAGE_5",
"ZCC_M_STKAGE_6",
"ZCC_M_STKAGE_7",
"ZCC_M_STKAGE_8",
"ZCC_M_STKAGE_9",
"ZCC_M_VALAGE",
"ZCC_M_VALAGE_1",
"ZCC_M_VALAGE_2",
"ZCC_M_VALAGE_3",
"ZCC_M_VALAGE_4",
"ZCC_M_VALAGE_5",
"ZCC_M_VALAGE_6",
"ZCC_M_VALAGE_7",
"ZCC_M_VALAGE_8",
"ZCC_M_VALAGE_9",
"ZCC_M_INACTSTK_QC",
"ZCC_M_INACTVAL_QC",
"ZCC_M_INACTSTK_NOW",
"ZCC_M_INACTVAL_NOW",
"ZCC_M_INACTVAL_RATE",
"ZBASE_UOM",
"ZBASE_UOM___T"
FROM "_SYS_BIC"."WLBI2.AL.MM/CVC_MM_STOCKAGE"
('PLACEHOLDER' = ('$$IN_QUERYDATE$$', '$(vDAY)'));
Concatenate
Load * from [lib://QVD (qlik-pro_aria)/inventory_v1.qvd](qvd);
Store inventory_v2 into [lib://QVD (qlik-pro_aria)/inventory_v1.qvd](qvd);
My question is:
If I click "load data" in the second application twice within a day, the data of that day will be duplicated. So I want to find some way to prevent this sitiation.
My plan is: if the CALDAY of inventory_v2 has already exist in inventory_v1, the data will not be concatenate into inventory_v1.
How should I write the script to make my plan works?
I tried this but it didn't work :
('PLACEHOLDER' = ('$$IN_QUERYDATE$$', '$(vDAY)'));
Concatenate
Load * from [lib://ibmQVD (qlik-pro_ibm.aria)/inventory_v1.qvd](qvd)where not Exists ("CALDAY");
Store inventory_v2 into [lib://ibmQVD (qlik-pro_ibm.aria)/inventory_v1.qvd](qvd);
Thanks!
