Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am trying to load data from a QVD where records are equal to one m,onth less than max (date)
Please go through the attached QVW, which explains my requirement.
Thanks
May be something along these lines:
Table:
load * inline [
ID, Date, Status1,Status2
1,7/30/2016,Y,Y
1,8/30/2016,Y,N
1,9/30/2016,Y,Y
1,10/30/2016,Y,Y
2,7/30/2016,Y,N
2,8/30/2016,N,Y
2,9/30/2016,Y,Y
2,10/30/2016,Y,Y];
MaxLoad:
LOAD AddMonths(Max(Date), -1) as ReqDate
Resident Table;
LET vReqDate = Num(Peek('ReqDate'));
TRACE $(vReqDate);
DROP Table MaxLoad;
FinalTable:
NoConcatenate
LOAD *
Resident Table
Where Date = $(vReqDate);
DROP Table Table;
Max date is 10/13/2016 and one month before that date would 9/13/2016? Why are you looking to pull 9/9/2016? Is it because it is the closest date to the Max Date - 1? So you would always want to pull one record in total? I think you will have to provide some more details, before one of us can help you out here
Hi Nicholas, try with this script:
data:
LOAD ID, Date#(Date, 'MM/DD/YYYY') as Date, Status Inline [
ID, Date, Status
1,7/12/2016,Y
1,8/10/2016,Y
1,9/09/2016,Y
1,10/12/2016,Y
2,7/18/2016,Y
2,8/22/2016,Y
2,9/02/2016,Y
2,10/13/2016,Y];
MaxDate_LastMonth:
LOAD AddMonths(Max(Date), -1) as MaxDate_LastMonth
Resident data;
LET vMinDate = MonthStart(Peek('MaxDate_LastMonth'));
LET vMaxDate = MonthEnd(Peek('MaxDate_LastMonth'));
MaxDateToFilter:
LOAD Max(Date) as DateToFilter
Resident data Where Date>='$(vMinDate)' and Date<='$(vMaxDate)';
DROP Tables data, MaxDate_LastMonth;
// DateToFilter contains the date, you can extract to a variable for use in 'where condition' with:
// LET vDateToFilter = Peek('DateToFilter');
HI Sunny,
Sorry about the insuffie QVW.
My example data is
load * inline [
ID, Date, Status1,Status2
1,7/30/2016,Y,Y
1,8/30/2016,Y,N
1,9/30/2016,Y,Y
1,10/30/2016,Y,Y
2,7/30/2016,Y,N
2,8/30/2016,N,Y
2,9/30/2016,Y,Y
2,10/30/2016,Y,Y];
The above data is in QVD
i want load only
1,9/30/2016,Y,Y and 2,9/30/2016,Y,Y into QVW from the above QVD data. (records which are associated to one month less than max month)
Can you please help me ?
Thanks
May be something along these lines:
Table:
load * inline [
ID, Date, Status1,Status2
1,7/30/2016,Y,Y
1,8/30/2016,Y,N
1,9/30/2016,Y,Y
1,10/30/2016,Y,Y
2,7/30/2016,Y,N
2,8/30/2016,N,Y
2,9/30/2016,Y,Y
2,10/30/2016,Y,Y];
MaxLoad:
LOAD AddMonths(Max(Date), -1) as ReqDate
Resident Table;
LET vReqDate = Num(Peek('ReqDate'));
TRACE $(vReqDate);
DROP Table MaxLoad;
FinalTable:
NoConcatenate
LOAD *
Resident Table
Where Date = $(vReqDate);
DROP Table Table;