Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading excel reports which need only some content rotated

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

5 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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
MVP
MVP

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?

Not applicable
Author

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 CtrMetric NameJuneJulyAugustSeptemberOctoberNovember

4001

Number of partners111222
4001Number of staff202019202323
4001Number of support staff3xxx
4002Number of partners2x
4002Number of staff15xx
4002Number of support staff
4003Number of partnersx
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
MVP
MVP

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;