topic Re: Load, where, exclude values by date in QlikView

Load, where, exclude values by date

Anonymous — Wed, 18 Nov 2015 12:00:14 GMT

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.

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

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
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

tresB — Wed, 18 Nov 2015 12:25:02 GMT

today()

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

sunny_talwar — Wed, 18 Nov 2015 12:25:33 GMT

Try this:

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

Gysbert_Wassenaar — Wed, 18 Nov 2015 12:27:30 GMT

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 — Wed, 18 Nov 2015 12:40:52 GMT

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
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 — Wed, 18 Nov 2015 13:51:27 GMT

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