Load, where, exclude values by date

[Anonymous]

hey Guys

Im sure you can help me quite quickly on this…

I like to exclude values from the loading process of a data model.

1. The classification is done by date (numeric value) and I like to load just the data, from today back 3 yearss

• %DateProcess = numeric date

Now I could use just this formula in the loading script from these 2 tables and it would work for now…

Dim_Calendar_Process:
NoConcatenate LOAD
%Date as %DateProcess,
x

x

x
FROM
$(dirQVD)_Generic\Dim_Calendar.qvd
(qvd)

Where %DateProcess >= ('numeric Value of the date');

My question Is whats the syntax for today in QV?

Where %DateProcess >= (today – 1000 (days))

Many thansk

[tresesco]

today()

Try like:    Where %DateProcess >= (today() – 1000)

[sunny_talwar]

Try this:

Where Num(%DateProcess) >= Num(AddYears(Today(), -3));

[Gysbert_Wassenaar · Accepted solution]

Where %DateProcess >= num(today()–1000) to go back 1000 days

or Where %DateProcess > num(AddYears(today(),–3)) to get the last 3 years.

[Kushal_Chawda]

One more way

// Create a dates for last 3 years from today

Dates:

LOAD date(addyears(Today(),-3)+IterNo()-1,'DD-MM-YYYY') as Date_Check

AutoGenerate(1)

While addyears(Today(),-3)+IterNo()-1 <= Today();

Dim_Calendar_Process:
NoConcatenate LOAD
%Date as %DateProcess,
x

x

x
FROM
$(dirQVD)_Generic\Dim_Calendar.qvd
(qvd)

Where exists (Date_Check,%DateProcess);

drop table Dates;

Note - Make sure format of the Date_Check & %DateProcess should be same

[Anonymous]

i believe i have to use %Date instead of %DateProcess

%Date as %DateProcess

tried both solutions by using %Date and %DateProcess

both version are working fine by using %Date

Many thanks