Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

asmithbi
Contributor II

Rolling Monthly Data Fields

Hello,


I am developing a balance sheet dashboard based on the Monthly Data Bucket fields below.  The data fields are rolling values.  Meaning, based on the naming convention of the fields, the period reference remains the same (i.e. CurrYr_Jan, PriroYr1_Jan) however, the transaction Month-Year will change. 

For example, Jan-2015 = Balance_CurrYr_Jan and Jan-2014 = Balance_PriorYr1_Jan.  Then when the calendar crosses to 2016, Jan-2015 becomes Balance_PriorYr1_Jan and Jan-2014 becomes Balance_PriorYr2_Jan and so on across all periods through PriorYr3. 

I have a transaction date field that I am using as the master calendar that includes the following date fields

  • MM/DD/YYYY
  • MM-YYYY
  • Month
  • Year
  • Quarter End

I am not sure how to code the load script to create such a cross-reference table that will roll as the years move forward.

Thank you in advance for any assistance on this development. 

Monthly Data Buckets:

Balance_CurrYr_Jan,

Balance_ CurrYr _Feb,

Balance_ CurrYr _Mar,

Balance_ CurrYr _Apr,

Balance_ CurrYr _May,

Balance_ CurrYr _Jun,

Balance_ CurrYr _Jul,

Balance_ CurrYr _Aug,

Balance_ CurrYr _Sept,

Balance_ CurrYr _Oct,

Balance_ CurrYr _Nov,

Balance_ CurrYr _Dec,

Balance_PriorYr1_Jan,

Balance_PriorYr1_Feb,

Balance_PriorYr1_Mar,

Balance_PriorYr1_Apr,

Balance_PriorYr1_May,

Balance_PriorYr1_Jun,

Balance_PriorYr1_Jul,

Balance_PriorYr1_Aug,

Balance_PriorYr1_Sept,

Balance_PriorYr1_Oct,

Balance_PriorYr1_Nov,

Balance_PriorYr1_Dec,

Balance_PriorYr2_Jan,

Balance_PriorYr2_Feb,

Balance_PriorYr2_Mar,

Balance_PriorYr2_Apr,

Balance_PriorYr2_May,

Balance_PriorYr2_Jun,

Balance_PriorYr2_Jul,

Balance_PriorYr2_Aug,

Balance_PriorYr2_Sept,

Balance_PriorYr2_Oct,

Balance_PriorYr2_Nov,

Balance_PriorYr2_Dec,

Balance_PriorYr3_Jan,

Balance_PriorYr3_Feb,

Balance_PriorYr3_Mar,

Balance_PriorYr3_Apr,

Balance_PriorYr3_May,

Balance_PriorYr3_Jun,

Balance_PriorYr3_Jul,

Balance_PriorYr3_Aug,

Balance_PriorYr3_Sept,

Balance_PriorYr3_Oct,

Balance_PriorYr3_Nov,

Balance_PriorYr3_Dec,

Tags (1)
1 Solution

Accepted Solutions

Re: Rolling Monthly Data Fields

Hi,

May be like this..

CrossLoad your Table, Then use the Mapsupstring function to replace the value.. Like

T1:

CrossTable(YearMonth, Data)

LOAD GLAccount,

     Balance_CurrYr_Jan,

     Balance_CurrYr_Feb,

     Balance_CurrYr_Mar,

     Balance_CurrYr_Apr,

     Balance_CurrYr_May,

......

....

From Source;

After Cross Load..

MAPField:

Mapping

LOAD * INLINE [

    F1, F2

    Balance_CurrYr_, 2015

    Balance_PriorYr1_, 2014

    Balance_PriorYr2_, 2013

    Balance_PriorYr3_, 2012

];

T2:

LOAD GLAccount,Data,MonthName(Date#(Left(MapSubstring ('MAPField', YearMonth),7),'YYYYMMM')) as MonthYear Resident T1;

DROP Table T1;

Check the Attachment. May be helps..

6 Replies

Re: Rolling Monthly Data Fields

Are all these fields with multiple rows of data you are looking to create or will Balance_Prior_blahblah will always have one value for each one of them???

asmithbi
Contributor II

Re: Rolling Monthly Data Fields

Hello Sunny,

Each field contains one value per GL account number.

For example:

GL Account     Balance_CurrY_Jan      Balance_CurrY_Feb      Balance_CurrY_Mar      etc...

1000                         $1234                              $5678                              $9012

1500                         $4565                              $8765                              $6567

2000                         $2346                              $9268                              $2347

2500                         $7659                              $4568                              $8768

Re: Rolling Monthly Data Fields

Hi,

May be like this..

CrossLoad your Table, Then use the Mapsupstring function to replace the value.. Like

T1:

CrossTable(YearMonth, Data)

LOAD GLAccount,

     Balance_CurrYr_Jan,

     Balance_CurrYr_Feb,

     Balance_CurrYr_Mar,

     Balance_CurrYr_Apr,

     Balance_CurrYr_May,

......

....

From Source;

After Cross Load..

MAPField:

Mapping

LOAD * INLINE [

    F1, F2

    Balance_CurrYr_, 2015

    Balance_PriorYr1_, 2014

    Balance_PriorYr2_, 2013

    Balance_PriorYr3_, 2012

];

T2:

LOAD GLAccount,Data,MonthName(Date#(Left(MapSubstring ('MAPField', YearMonth),7),'YYYYMMM')) as MonthYear Resident T1;

DROP Table T1;

Check the Attachment. May be helps..

asmithbi
Contributor II

Re: Rolling Monthly Data Fields

Thank you Settu,

I will test this and will let you know the result.

Thank you very much for your help!!!

asmithbi
Contributor II

Re: Rolling Monthly Data Fields

Hello Settu,

Your solution worked very well.  Thank you!!  I created a balance sheet and are pulling correct balances per GL account per period.   

Thank you again!

Re: Rolling Monthly Data Fields

Good to know.. You are Welcome!