I have to implement the incremental logic for a table. So when the first data load I need to pick up all the records from the source system and from next refresh it needs to take the max_date(combination of Year,Period& Day) from QVD and it need to fetch data beyond that then it need to be updated for next run.
Let v_budget= isnull(QvdCreateTime('$(vQVD)Weekly_budget1.qvd')); //Checking the Historical Qvds existance
if v_budget=-1 then //IF QVD Not present
Trace History QVD Not available;
/***Daily budget Historical****/
Weekly_budget:
SQL select *
from budget_monthly_f
where plant_id='1060';
Store Weekly_budget into $(vQVD)\Weekly_budget1.qvd;
DROP TABLE Weekly_budget;
Weekly_budget:
LOAD *, Date(Date#(year&'-'&period&'-'&day, 'YYYY-MM-DD')) as max_date
FROM [$(vQVD)Weekly_budget1.qvd](qvd);
Store Weekly_budget into $(vQVD)\Weekly_budget1.qvd;
Drop Table Weekly_budget;
ELSE
// If Historical QVD available
Trace History QVD available;
Weekly_budget:
LOAD *
FROM [$(vQVD)Weekly_budget1.qvd](qvd);
tab:
Load max(date#(max_date),'YYYY-MM-DD') as MaxDate_budget
resident Weekly_budget;
Let vvmax=Text(Date(peek('MaxDate_budget'), 'YYYY-MM-DD'));
Drop table tab;
Concatenate
SQL select *
from budget_monthly_f
Where '$(vvmax)' > concat(year,'-',period,'-',day)
and plant_id='1060'
;
Store Weekly_budget into $(vQVD)\Weekly_budget1.qvd;
Drop Table Weekly_budget;
End if
Here my question for the 2,3rrd or other consecutive refresh will it take max_date automatically or do I need to update that in this script
For incremental load, i think you need to modify condition as below.
Where concat(year,'-',period,'-',day) >'$(vvmax)'
and plant_id='1060'
And whenevr you execute this statement,
SQL select *
from budget_monthly_f
where plant_id='1060';
Store Weekly_budget into $(vQVD)\Weekly_budget1.qvd, You will get different year&'-'&period&'-'&day which is your max key.. but i am afraid it would not work if you refresh it 2 times a day cz value of day will not change if you refresh it again the same day
