Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm working on a fiscal calendar and can't seem to force the row above my current Fiscal Quarter flag to equal 1 (for a prior Fisc. Quarter flag). I have distinctly loaded Fisc. Year, Fisc. Quarter, and their respective current flags:
NoConcatenate
FiscCalPrevQuarter:
LOAD Distinct
FiscalYear,
FiscalQuarter,
cFYr_Flag,
cFQ_Flag
//if(cFQ_Flag = 1 and Previous(cFQ_Flag) = 0, 1, 0) as pFQFlag //<< garbage
Resident FiscCalFlags
Order by FiscalYear, FiscalQuarter asc
;
DROP Table FiscCalFlags;
I want my table to look like the attached image, with the pFQ flag marking '1' where the prior fiscal quarter was. This way, i can left join it back in the full calendar and mark all of the corresponding dates for that prior quarter to be flagged with a '1' as well.
I've attached a qvw with the quarters and flags. I've tried a number of things with peek() and previous() and can't seem to get this. I just want a 1 in a pFQ_Flag field at the row above the cFQ_Flag field (currently for this case, 2015 Q4 = 1).
Thanks a lot,
Try this:
Cal:
LOAD FiscalYear,
FiscalQuarter,
cFQ_Flag,
cFYr_Flag
FROM
TB06_20160204_151706.xls
(biff, embedded labels, table is Sheet1$);
Final:
LOAD *,
Alt(Peek('cFQ_Flag'), 0) as pFQFlag
Resident Cal
Order By FiscalYear desc, FiscalQuarter desc;
DROP Table Cal;
is this even possible since the row above cFQ_Flag is already populated by 0's?
Try this:
Cal:
LOAD FiscalYear,
FiscalQuarter,
cFQ_Flag,
cFYr_Flag
FROM
TB06_20160204_151706.xls
(biff, embedded labels, table is Sheet1$);
Final:
LOAD *,
Alt(Peek('cFQ_Flag'), 0) as pFQFlag
Resident Cal
Order By FiscalYear desc, FiscalQuarter desc;
DROP Table Cal;
Thanks!