Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rpayn01215
Contributor

Creating data on the row above (fiscal calendar)

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,

1 Solution

Accepted Solutions

Re: Creating data on the row above (fiscal calendar)

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;


Capture.PNG

3 Replies
rpayn01215
Contributor

Re: Creating data on the row above (fiscal calendar)

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

Re: Creating data on the row above (fiscal calendar)

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;


Capture.PNG

rpayn01215
Contributor

Re: Creating data on the row above (fiscal calendar)

Thanks!

Community Browser