Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
.... |
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
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
Thanks
If the data set is big enough. I don't know which three years is the latest three year...?
Thanks!
If I use the today(), the data just count back from the date of today not the latest day in the dataset....?
So, you can take the Max(Yearmonth) and use the function AddMonths( Max(Yearmonth) ,-36)
BR
Ariel
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
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)
;