Creating data on the row above (fiscal calendar)

[Anonymous]

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,

[Anonymous]

is this even possible since the row above cFQ_Flag is already populated by 0's?

[sunny_talwar · Accepted solution]

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;

[Anonymous]

Thanks!