Calculating Months

[tmumaw]

I have a QVW which only has GL Account, office and fiscal year on it.  The months then are broken down into different rows (HS01 for Jan with dollars, HS02 with dollars for Feb, etc.).  Any ideas how to associate these columns to months and have a calendar in my model.

[bbi_mba_76]

Hi,

I don't understand, maybe with right 2 characters of the field cointaining HS01, HS02?

Your rows are

[GL Account] [office] [fiscal year] [HS type] [Measure]

or it is like a pivot

[GL Account] [office] [fiscal year] [HS01] [HS02]...[HS12]

?

[tmumaw]

It's like the pivot table.

[pgrenier · Accepted solution]

Hello Thom,

I believe this script might help you. In order to simulate the presence of your QVD file, I have added a LOAD * Inline statement to reproduce a similar table as a first step. Following that, you shall notice a LOAD instruction that is preceded by the Crosstable() instruction. What this instruction does is take all column names and values after the first n columns, and creates two new fields bearing the specified names (MonthNum and Sales). From this point, a new load statement is contructed on the previous result in order to extract the month number from the string HS##. The final instruction DROP Tables cleans up the environment, leaving only the Data table for you to analyse.

QVD_Content:

LOAD * Inline [

GL Account, Office, Fiscal Year, HS01, HS02, HS03, HS04, HS05, HS06, HS07, HS08, HS09, HS10, HS11, HS12

31135, 1A, 2013, 16.23, 12.11, 16.24, 164.56, 61.51, 1651.49, 148.94, 1498.41, 19.81, 948.19, 19.41, 919.91

14516, 1B, 2013, 91.19, 94.19, 16.23, 12.11, 16.24, 164.56, 61.51, 1651.49, 148.94, 1498.41, 19.81, 948.19

13585, 1A, 2013, 164.56, 61.51, 1651.49, 148.94, 1498.41, 19.81, 948.19, 19.41, 919.91, 91.19, 94.19, 16.23

13514, 1B, 2013, 12.11, 16.24, 164.56, 61.51, 1651.49, 148.94, 1498.41, 16.23, 12.11, 16.24, 164.56, 61.51];

Data_tmp:

CrossTable(MonthNum, Sales, 3)

LOAD *

Resident QVD_Content;

Data:

LOAD [GL Account], Office, [Fiscal Year], Right(MonthNum, 2) as Month, Sales

Resident Data_tmp;

DROP Table QVD_Content, Data_tmp;

Regards,

Philippe

[jonathandienst]

Thom

I suggest the following:

• I would transpose the data so that HS01, HS02, etc are columns
• Then I would crosstable load that so that we have a period field containing HS01, HS02, etc
• I would use an inline load to create an association with months. Something like:

     Periods:

     LOAD Period,

          Month(MakeDate(Year(Today), Month)) As Month

     [ Period, Month

          HS01, 1

          HS02, 2

          ....

     ];

You could also Mapping LOAD the inline table before the crosstable load, and a Map Using like this:

     Map Period Using PeriodMap;

Hope that helps

Jonathan

[tmumaw]

Philippe,

Thank you very much.  That was exactly what I was looking for.

Thom