topic Re: Calculating Months in QlikView

Calculating Months

tmumaw — Tue, 15 Oct 2013 12:01:00 GMT

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.

Re: Calculating Months

bbi_mba_76 — Tue, 15 Oct 2013 12:18:15 GMT

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]

Re: Calculating Months

tmumaw — Tue, 15 Oct 2013 12:23:25 GMT

It's like the pivot table.

Re: Calculating Months

pgrenier — Tue, 15 Oct 2013 12:40:33 GMT

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

Re: Calculating Months

jonathandienst — Tue, 15 Oct 2013 13:25:36 GMT

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

Re: Calculating Months

tmumaw — Tue, 15 Oct 2013 13:27:47 GMT

Philippe,

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

Thom