Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How this script works for creating period ?

Can somebody help me in understanding the below code . I am completely fed up after trying so many times.

// Add Period1 field to be able to associate with the AsOf table

REV:

Load

MonthStart(Date(Date(Date#(MonthYear,'MMM-YYYY'),'DD-MM-YYYY'),'YYYYMM')) as Period1,

     MonthName(MonthNo) as Month

From REV.qvd(qvd);

// Temporary table with list of Period values

PeriodTbl:

LOAD date(fieldvalue('Period1',recno()),'YYYYMM') as Period

AUTOGENERATE fieldvaluecount('Period1');

AsOf:

//Load every month as current month into AsOf table

LOAD

Period as AsOfPeriod

,'Current' as PeriodType

,Period as Period1

,Year(Period) as Year

RESIDENT PeriodTbl;

CONCATENATE (AsOf)

//Load Rolling 3 into AsOf table

LOAD

Period as AsOf

,'Rolling 3' as PeriodType

,date(addmonths(Period,1-iterno()),'YYYYMM') as Period1

,Year(Period) as Year

RESIDENT PeriodTbl

WHILE iterno() <= 3;

CONCATENATE (AsOf)

//Load Rolling 6 into AsOf table

LOAD

Period as AsOf

,'Rolling 6' as PeriodType

,date(addmonths(Period,1-iterno()),'YYYYMM') as Period1

,Year(Period) as Year

RESIDENT PeriodTbl

WHILE iterno() <= 6;

CONCATENATE (AsOf)

//Load Rolling 12 into AsOf table

LOAD

Period as AsOf

,'Rolling 12' as PeriodType

,date(addmonths(Period,1-iterno()),'YYYYMM') as Period1

,Year(Period) as Year

RESIDENT PeriodTbl

WHILE iterno() <= 12;

CONCATENATE (AsOf)

//Load YTD into AsOf table

LOAD

Period as AsOf

,'YTD' as PeriodType

,date(addmonths(Period,1-iterno()),'YYYYMM') as Period1

,Year(Period) as Year

RESIDENT PeriodTbl

WHILE iterno() <= num(month(Period));

INNER JOIN (AsOf)

LOAD Period as Period1

RESIDENT PeriodTbl;

DROP TABLE PeriodTbl;

Thanks,

James

3 Replies
sunny_talwar

You don't understand the code or is there a problem in the code you are trying to resolve?

fvelascog72
Partner - Specialist
Partner - Specialist

Read this:

The As-Of Table

Not applicable
Author

I am not able to understand the code and how this will generate the table. Like what will be the sample data.