Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following file of excel and qwd.
I would like to get the last 12 months with data via script.
So I would like to get the following data.
201606 21,1%
201607 18,6%
201608 18,2%
201609 23,1%
201610 24,9%
201611 28,8%
201612 24,9%
201701 29,0%
201702 31,4%
201703 30,6%
201704 28,1%
201705 31,5%
thank you
Here you go
Script:
Sample:
LOAD Date,
margin
FROM
margin.xls
(biff, embedded labels, table is Sheet1$) Where Date >= $(vRunning12Months) and Date <= $(vLast);
LET vRunning12Months = Peek('Date',-13,'Sample');
LET vLast = Peek('Date', -2, 'Sample');
PFA
Hi Pablo,
You can use month to day function
Here you go
Script:
Sample:
LOAD Date,
margin
FROM
margin.xls
(biff, embedded labels, table is Sheet1$) Where Date >= $(vRunning12Months) and Date <= $(vLast);
LET vRunning12Months = Peek('Date',-13,'Sample');
LET vLast = Peek('Date', -2, 'Sample');
PFA
Try to load it dynamically suppose if in your table dates are not in the proper ascending order then you not get max date, so try this ways by finding maxdate and then load data from maximum date upto 12 months YYYYMM
Data:
LOAD Date, margin
FROM margin.xls (biff, embedded labels, table is Sheet1$);
MaxDateTab:
LOAD Max(Date) as MaxDate Resident Data;
LET vLast12Month = Date( AddMonths( Date#( Peek('MaxDate',0,'MaxDateTab'), 'YYYYMM'),-12), 'YYYYMM');
LET vMaxDate = Peek('MaxDate', 0, 'MaxDateTab');
NoConcatenate
LOAD * Resident Data Where Date > $(vLast12Month);
DROP Table Data;
OupPut