Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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]
?
It's like the pivot table.
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
Thom
I suggest the following:
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
Philippe,
Thank you very much. That was exactly what I was looking for.
Thom