Filter data when loading

[#1]

Hi, I have a SampleData set attached.

In the sample data I have a column called Yearmonth. When I load the data from this data set, I would like to choose the lastest 3 year data. How should I write for the script? Thanks!

Yearmonth

201103

201403

201203

....

[magavi_framsteg]

Hi Jelly.

In pseudo code it would be something like this:

load ...

where Yearmonth > date(addmonths(now(), -36), 'YYYYMM')

You need to tweak the expression to get the full third year to something like:

load ...

where

year(AddMonths(today(), -36)) & 01

[ariel_klien]

Hi,

try this:

LOAD Yearmonth,

     [Percent of pass],

     Grade

FROM

C:\Users\arielkl\Downloads\SampleData.xlsx

(ooxml, embedded labels, table is Sheet1)

where match(Yearmonth,201103,201403,201203)

BR

Ariel

[#4]

Thanks

If the data set is big enough. I don't know which three years is the latest three year...?

[#5]

Thanks!

If I use the today(), the data just count back from the date of today not the latest day in the dataset....?

[ariel_klien]

So, you can take the Max(Yearmonth) and use the function AddMonths( Max(Yearmonth) ,-36)

BR

Ariel

[#7]

Try to put in Excel and Load the excel sheet in script.

Then, define SET variable and put this variable in where.

Regards

Amol Khochare

[maxgro]

you can use a two step load:

Directory;

// calculate min year month to load

load max(Yearmonth) as MaxYearMonth

FROM

SampleData.xlsx

(ooxml, embedded labels, table is Sheet1);

LET vMax =date(addmonths(date#(Peek('MaxYearMonth'), 'YYYYMM'), -36), 'YYYYMM');

trace $(vMax);

// load from min year month

LOAD Yearmonth,

     [Percent of pass],

     Grade

FROM

SampleData.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Yearmonth >= $(vMax)

;