topic Help on Incremental Logic in QlikView

Help on Incremental Logic

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

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

-Jai

Re: Help on Incremental Logic

shiveshsingh — Mon, 29 Jan 2018 12:15:06 GMT

Whenever you'll refresh the QVD, Max_Date will automatically refreshed based on definition that you have provided in script.

Re: Help on Incremental Logic

Anonymous — Mon, 29 Jan 2018 13:48:14 GMT

Hi shiveshsingh‌

Thanks for your comments. I feel still it is missing the max date field in the 3rd and consecutive load. So I guess

below changes required

select *,concat(year,'-',period,'-',day) as max_date

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

let me know,if any

-Jai

Re: Help on Incremental Logic

shiveshsingh — Tue, 30 Jan 2018 06:11:42 GMT

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