Loading excel reports which need only some content rotated

[#1]

Hi,

I need to load an Excel file that is a report from SAP which has profit centres and metrics (e.g., number of people in a profit centre) down the left and months across the top.

I need the data to have dimensions of months, profit centres and facts of the metrics.

To do this, I am trying to use the transformation in load from table file.

I can easily get rid of the extraneous rows and columns easily but when it comes to transpose, it seems to be all or nothing - I only want to rotate colums (say C-M) and leave columns A and B as they are.

Any thoughts. I don't really want to have to do this in Excel and getting IT to change the report will be ridiculously long time in our environment.

Thanks

Nathan

[swuehl]

I think you don't need to rotate, you need to use the Crosstable load. This is also available from the assistent in load script dialog, and please check the manual for Crosstable Load.

If you post a small sample of your excel file, I think someone could help you with the syntax.

Regards,

Stefan

[#3]

Hi Stefan,

Thanks a lot, I'd never seen the cross table load before. I've got that to work and that was veryuseful.

It works to a point but it's only half-way there - ideally I'd have the metric names as items in t heir own right, but that might not be possible.

thanks again

Nathan

[swuehl]

Hm, sorry I don't quite understand what you mean with "metric names as items in their own right", could you explain that for me? And may be post some lines of your table here?

[#5]

Hi Stefan,

Here's a mock up of my table. I have a lot of these in varying forms, all using a typical SAP extract approach, with months at the top.

Profit Ctr	Metric Name	June	July	August	September	October	November
4001	Number of partners	1	1	1	2	2	2
4001	Number of staff	20	20	19	20	23	23
4001	Number of support staff	3		x	x	x
4002	Number of partners	2				x
4002	Number of staff	15			x	x
4002	Number of support staff
4003	Number of partners				x
etc

Etc...

So what I would like, is to have the months in a column, and the 'metric names' as column headers. So I could then use 'number of partners, number of staff' as expressions in charts etc. (I can see the value of where I am at the moment, but it looks like it will be tricky to do comparisons, e.g., ratio of staff to partners etc)

thanks for your help,

nathan

[swuehl · Accepted solution]

Maybe like this?

CrossTable:

CrossTable(Month, Data, 2)

LOAD [Profit Ctr],

     [Metric Name],

     June,

     July,

     August,

     September,

     October,

     November

FROM

[http://community.qlik.com/thread/38836?tstart=0]

(html, codepage is 1252, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 9))

));

MetricNameTable:

LOAD distinct [Metric Name] as MetricName resident CrossTable;

let vNum = NoOfRows('MetricNameTable');

For vIter = 1 to vNum 

Let vName = FieldValue('MetricName',vIter);

Result:

Concatenate Load

[Profit Ctr],

Month,

Data as [$(vName)]

resident CrossTable where [Metric Name] = '$(vName)';

Next

drop field MetricName;

drop table CrossTable;