Only load certain dates from qvd

[groveliam]

Is there a way to only load in certain dates from a qvd? I need it to only read in the last 60 days, month ends, and January 2nd. Right now I have tried:

let MinDate = Date((Today() - 60), 'YYYY-MM-DD');

let monthEnds = Date(MonthEnd(AddMonths(today()-60,-1)), 'YYYYMMDD');

    Load * From [lib://Files\Deposit.qvd] (qvd)

    where [Production Date] >= $(MinDate);

    Concatenate

    Load * From [lib://Files\Deposit.qvd] (qvd)

    where [Production Date] <= $(monthEnds);

but this does not seem to affect the dates loaded in at all. Any help is appreciated!

Thanks,

Liam Grover

[sunny_talwar · Accepted solution]

May be create a list of dates using AutoGenerate  and then use Where Exists... something like this

Table:

LOAD Today() - IterNo() + 1 as [Production Date]

AutoGenerate 1

While IterNo() <= 60;

Concatenate (Table)

LOAD Floor(MonthEnd(Today() - 60, -IterNo()+1) as [Production Date]

AutoGenerate 1

While IterNo() <= 20; -> You can Change this number based on how far back you wish to go

Concatenate (Table)

LOAD Num(MakeDate(2018, 1, 2)) as [Production Date]

AutoGenerate 1;

FactTable:

Load * From [lib://Files\Deposit.qvd] (qvd)

where Exists([Production Date]);

DROP Table Table;

This will also ensure that you have an optimized load from your qvd file.

[groveliam]

Thank you for the quick answer that worked great!