Help Required in Handling Updates in Incremantal Loading using QVD
Hi,
The requirement was to load the last one year data in QlikView application. I am doing it using Incremental Loading. Below is the load script.
I need help in implementaing the below things.
1) If the QVD file is removed the load script fails, stating it can't find the QVD file. So how can i implement the functionality if QVD is missing it should do full reload for last 1 year.
This means that in below condition '$(MaxID)' is replaced by date - 365
"D_SYSTEM_LOGS"."V_QV_Canary_Day_KPI_A_W_"
where logdate > '$(MaxID)'.
2) Secondly once load one year data in QVD , after that daily data is loaded and concatenated with the QVD. If there is any changes in data for previous days in Teradata database, it is not updated in QVD. How can i implement this functionality that instead to do full reload for updated data i juts update the QVD with changed rows.
SET ThousandSep=' ';
SET DecimalSep='.';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep='.';
SET MoneyFormat='# ##0.00 €;-# ##0.00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
MaxKeyLoad:
load Date(max(logdate),'YYYY-MM-DD') as MaxID,Date(max(logdate)-365,'YYYY-MM-DD') as MaxID2
from
V_QV_Canary_Day_KPI_A_W_.qvd (qvd);
Let MaxID=peek('MaxID',0,MaxKeyLoad);
Let MaxID2=peek('MaxID2',0,MaxKeyLoad);
ODBC CONNECT TO QlikView;
V_QV_Canary_Day_KPI_A_W_:
Load
"Cnt_1",
"Cnt_15",
"Cnt_1_3",
"Cnt_3",
"CNT_50_Pct",
"CNT_75_Pct",
"Cnt_8",
"Cnt_8_15",
"CNT_90_Pct",
"CNT_Less50_Pct",
"Concurrency_AVG",
"day_of_week",
logdate,
"Month_of_Calendar_Name",
"month_of_year",
"Month_of_Year_Name",
QueryId,
QueryName,
"Responsetime_AVG",
"Responsetime_STDEV",
"Week_of_Calendar_Name",
"week_of_year",
weekend,
"year_of_calendar",
Month(logdate) AS Month_Name;
SQL SELECT "Cnt_1",
"Cnt_15",
"Cnt_1_3",
"Cnt_3",
"CNT_50_Pct",
"CNT_75_Pct",
"Cnt_8",
"Cnt_8_15",
"CNT_90_Pct",
"CNT_Less50_Pct",
"Concurrency_AVG",
"day_of_week",
logdate,
"Month_of_Calendar_Name",
"month_of_year",
"Month_of_Year_Name",
QueryId,
QueryName,
"Responsetime_AVG",
"Responsetime_STDEV",
"Week_of_Calendar_Name",
"week_of_year",
weekend,
"year_of_calendar"
FROM "D_SYSTEM_LOGS"."V_QV_Canary_Day_KPI_A_W_"
where logdate > '$(MaxID)';
Concatenate
Load
"Cnt_1",
"Cnt_15",
"Cnt_1_3",
"Cnt_3",
"CNT_50_Pct",
"CNT_75_Pct",
"Cnt_8",
"Cnt_8_15",
"CNT_90_Pct",
"CNT_Less50_Pct",
"Concurrency_AVG",
"day_of_week",
logdate,
"Month_of_Calendar_Name",
"month_of_year",
"Month_of_Year_Name",
QueryId,
QueryName,
"Responsetime_AVG",
"Responsetime_STDEV",
"Week_of_Calendar_Name",
"week_of_year",
weekend,
"year_of_calendar",
"Month_Name"
FROM V_QV_Canary_Day_KPI_A_W_.qvd (qvd)
where logdate >'$(MaxID2)';
store V_QV_Canary_Day_KPI_A_W_ into V_QV_Canary_Day_KPI_A_W_.qvd;
