Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

caccio88
New Contributor III

Use of "where" in load script

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.

gwassenaarbpn stalwar1 swuehl

1 Solution

Accepted Solutions

Re: Use of "where" in load script

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

4 Replies

Re: Use of "where" in load script

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

Highlighted
prieper
Honored Contributor II

Re: Use of "where" in load script

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

MVP
MVP

Re: Use of "where" in load script

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
New Contributor III

Re: Use of "where" in load script

This works! thx so much Peter!

Community Browser