Transpose only some columns from excel file

[#1]

Hello,

I have an excel file with columns: Client; type of business; Jan; Feb; Mar; ...

Client	Type of business	Jan	Feb	Mar	Apr	May	...
A	Booked	123	123	234	433	567
A	New business	34534	375465	354	708	9867
B	Booked	123	123	234	433	567
B	New business	34534	375465	354	708	9867

And I want to transpose only the month columns to turn time into a dimension along with Client and Type of Business. I want to load it to QV like this:

Client	Type	Date	Amount
A	Booked	Jan	123
A	Booked	Feb	123
A	Booked	Mar	234
A	Booked	Apr	433
A	Booked	May	567
A	Booked	...
A	New business	Jan	34534
A	New business	Feb	375465
A	New business	Mar	354
A	New business	Apr	708
A	New business	May	9867
A	New business	...
B	Booked	Jan	123
B	Booked	Feb	123
B	Booked	Mar	234
B	Booked	Apr	433
B	Booked	May	567
B	Booked	...
B	New business	Jan	34534
B	New business	Feb	375465
B	New business	Mar	354
B	New business	Apr	708
B	New business	May	9867
B	New business	...

How can I do this?

Thanks

Alexandra

[#2 · Accepted solution]

Crosstable( Month , Amout , 2)

Load Client , Type , Jan , Feb , Mar , ....

From your excelfile ;

The parameter 2 is use to keep the Client & the type as identification

JJ

[hector]

Hi, use the wizard and the option "Cross table"

Rgds

Edit: This happens when you don't refresh the page xD LOL

[#4]

That's it! Thanks

[rebmanna]

Hi,

ok, thats the Way for crosstable.

But can i do this?

Data-Vertikal (extracted from INI-Files)

nPtsNum	PrüfMerkmal	PrüfWert
4711	DMKONTROLLE_ABLAGEORT	1
4711	DMKONTROLLE_LASERQUALITÄT	AAAAA
4711	DMKONTROLLE_ZEITSTEMPEL	02.11.2011 06:00:16
4711	DURCHTRITTKOPFLEISTE_KONTURABFRAGE	IO
4711	DURCHTRITTKOPFLEISTE_STIFTABFRAGE	IO
4711	DURCHTRITTKOPFLEISTE_ZENTRIERUNG	IO
4711	KONTROLLEN_STATUSPK1	7
4711	KONTROLLEN_STATUSPK2	31
4711	KONTROLLEN_STATUSPK3	4095
4711	KONTROLLEN_ZEITSTEMPEL	02.11.2011 06:02:02
4711	KONTUR_ERGEBNIS	IO
4712	DMKONTROLLE_ABLAGEORT	3
4712	DMKONTROLLE_LASERQUALITÄT	AAAAB
4712	DMKONTROLLE_ZEITSTEMPEL	02.11.2011 07:10:22
4712	DURCHTRITTKOPFLEISTE_KONTURABFRAGE	NIO
4712	DURCHTRITTKOPFLEISTE_STIFTABFRAGE	IO
4712	DURCHTRITTKOPFLEISTE_ZENTRIERUNG	NIO
4712	KONTROLLEN_STATUSPK1	7
4712	KONTROLLEN_STATUSPK2	31
4712	KONTROLLEN_STATUSPK3	4095
4712	KONTROLLEN_ZEITSTEMPEL	02.11.2011 08:02:02
4712	KONTUR_ERGEBNIS	IO

...

Target-Table:

nPtsNum	DMKONTROLLE_ABLAGEORT	DMKONTROLLE_LASERQUALITÄT	DMKONTROLLE_ZEITSTEMPEL	...weitere Spalten ...	KONTUR_ERGEBNIS
4711	1	AAAAA	02.11.2011 06:00:16		IO
4712	3	AAAAB	02.11.2011 07:10:22		IO

...

„nPtsNum“ is the Key.

"Prüfmerkmal" should be the Columns in Target-Table

"PrüfWert" should be Data

I tried crosstable, transpose ... no success 😞

Some Ideas ?

Thanks,

Adrian

[swuehl]

Have you tried creating a pivot table chart with dimensions nPtsNum and DMKONTROLLE_ABLAGEORT and expression

= only(PrüfWert)

, dragging the second dimension to the top?

[rebmanna]

Hi,

thanks, but i dont have "DMKONTROLLE_ABLAGEORT" as dimension because it is as value in

Table Data-Vertikal.

In Target table i want to have it as dimension. But i dont have the transformed table.

Maybe GENERIC LOAD wil help?

But I dont know how it works.

Thanks,

Adrian

[swuehl]

Sorry, my mistake, I meant to say dimensions nPtsNum and PrüfMerkmal

[#9]

Look at this example.

I don't remember the guy who solve this issue but it's a fine solution.

It use a generic load

JJ

[rebmanna]

Great, thanks, very fine solution!