Use of "where" in load script

[caccio88]

Hello guys,

my final target is to exclude from the load values.

The field is a text field, it's the "Filename" of QVD but is composed like YearMonth.

Then I tried to use a variable to exclude always the "Filename" before the last 3 YearMonth.

This is my script but using where at the end of my load seems tha doesn't work.

Here is my script and my qvw.

//////////

let vLast3PrevM = date(Floor(MonthEnd(AddMonths(Today(),-3))),'YYYYMM');

LOAD * INLINE [

    Filename

    201701.QVD

    201702.QVD

    201703.QVD

    201704.QVD

]

where date(Floor(MonthEnd(AddMonths(Makedate(Mid(Filename,1,4),Mid(Filename,5,2),Mid(Filename,7,2)),0))),'YYYYMM') > $(vLast3PrevM);

/////////

Thanks for your help.

gwassenaar‌ bpn‌ stalwar1 swuehl

[Peter_Cammaert · Accepted solution]

Don't make it too difficult. Check this script:

let vLast3PrevM = Floor(MonthEnd(AddMonths(Today(),-3)));

LOAD * INLINE [

Filename

201701.QVD

201702.QVD

201703.QVD

201704.QVD

]

WHERE MakeDate(left(Filename, 4), Mid(Filename, 5, 2), 1) > $(vLast3PrevM);

[prieper]

not clear, what you wish to perform:

Load QVD's with a FileDate > than .....

or

Load QVD's with a certain string in the filename?

edit: Peter Cammaert was faster & solution works

[sunny_talwar]

Not sure if this data is from qvd, but if it is, you might be able to use this also

Temp:

LOAD Date(MonthStart(Today(), -3 + RecNo()), 'YYYYMM') & '.QVD' as Filename

AutoGenerate 3;

Table:

NoConcatenate

LOAD * INLINE [

    Filename

    201701.QVD

    201702.QVD

    201703.QVD

    201704.QVD

]

Where Exists(Filename);

DROP Table Temp;

[caccio88]

This works! thx so much Peter!