How to group column names as a new dimension?

[josephinetedesc]

Hi

I have a number of columns in a flat file.

For example: [Financial Year],[Financial Month], Company, Division,

[January Net Amount], [January YTD Amount], [January Bud Amount],

[February Net Amount], [February YTD Amount], [February Bud Amount] etc for every month.

How do I put in a dimension called January which will give me just those 3 January columns, another called February which will give me just those February columns?

Jo

[Anonymous]

when you are using any Chart

In the given SnapShot I have used 5 Column for the group January

So I dont think that you have to create any Super Column

Regards

Tahemas Momin

[josephinetedesc]

Thank you for this - what I did was look at the help section for Drilldown but ... it seemed to indicate that I needed to have a pivot chart in order to use a group to select the columns which would make up that group.  In which case I would then need to have to 12 expressions?

Jo

[josephinetedesc]

Hi Anand

This worked but I was wondering why was concatenation necessary?

Jo

[prieper]

Wouldn't it be easier to work with CROSSTABLE? This may prevent errors, if there are not all monthes in the flatfile.

AllData: CROSSTABLE (Item, Value, 4) LOAD * FROM FlatFile;
AllData_wMonth: LOAD *, SUBFIELD(Item, ' ', 1) AS Month RESIDENT AllData;
DROP TABLE AllData;
Data:
LOAD [Financial Year],[Financial Month], Company, Division, Month, Item     AS [NetAmount] RESIDENT AllData_wMonth WHERE WILDMATCH(Item, '*NET AMOUNT');
JOIN (Data) LOAD [Financial Year],[Financial Month], Company, Division, Month, Item     AS [YTD Amount] RESIDENT AllData_wMonth WHERE WILDMATCH(Item, '*YTD Amount');
JOIN (Data) LOAD [Financial Year],[Financial Month], Company, Division, Month, Item     AS [Bud Amount] RESIDENT AllData_wMonth WHERE WILDMATCH(Item, '*Bud Amount');
DROP TABLE AllData_wMonth;

Peter

[josephinetedesc]

Thanks Peter that worked too!

I think the simplest way -(at least I was able to follow it ... except for why concatenation!)

is the way that its.anandrjs  does it.

[prieper]

With Anand's solution you need to know, which months are listed in the file.

Concatenation is necessary as in the first step all data are filtered, which refer to 'January' with renaming of the fields to more generic names. The same is done with 'February', 'March' etc., which are just written beneath the 'January'-data.

[josephinetedesc]

Thank you

Jo