Column headers as rows (dimensions).

[#1]

I have an excel tab provided by accounting department. It has an inconvenient structure:

ID_company	Total_due	Total_paid	Total_diff	1Q15_due	1Q15_paid	1Q15_diff	2Q15_due	2Q15_paid	2Q15_diff
1	100000	50000	50000	50000	20000	30000	50000	30000	20000
2	200000	190000	10000	100000	100000	0	100000	90000	10000
3	150000	200000	-50000	50000	100000	-50000	100000	100000	0

The (main) problem is that every quarter another 3 columns will be added. So, I'd like the table to be transformed into something like that:

ID_company	Quarter	Due	Paid	Diff
1	1Q15	50000	20000	30000
1	2Q15	50000	30000	20000
2	1Q15	100000	100000	0
2	2Q15	100000	90000	10000
3	1Q15	50000	100000	-50000
3	2Q15	100000	100000	0

and automaticly expand the data zone when more quarters are added in the source file. Is it possible to do via the transformation step? The only way I know for me now is to write a small app using .NET that would spawn an Excel instance and move data in cycles. The source table is not large (about 2000*50 cells), so it won't take long, but I'm searching for some more elegant solution without running external apps. If possible. )) Any help appreciated.

[Colin-Albert]

Have a look at the CrossTable function.

The Crosstable Load

[#3]

I'm aware of the function, but how can the problem of new quarters be solved? Or I'm missing something?

[jonathandienst · Accepted solution]

This should do it:

T_Source:

CrossTable(T_Field, Value, 4)

LOAD ID_company,

  Total_due,

  Total_paid,

  Total_diff,

  1Q15_due,

  1Q15_paid,

  1Q15_diff,

  2Q15_due,

  2Q15_paid,

  2Q15_diff

FROM ....;

Result:

LOAD

  ID_Company,

  SubField(T_Field, '_', 1) As Quarter,

  If(SubField(T_Field, '_', 2) = 'due', Value) As Due,

  If(SubField(T_Field, '_', 2) = 'paid', Value) As Paid,

  If(SubField(T_Field, '_', 2) = 'diff', Value) As Diff

Resident T_Source;

DROP Table T_Source;

You could replace the first LOAD with LOAD * ... if the number of quarters changes.

[MayilVahanan]

Hi

Try like this

Temp:

CrossTable(Period, Data,1)

LOAD * Inline

[

ID_company,Total_due,Total_paid,Total_diff,1Q15_due,1Q15_paid,1Q15_diff,2Q15_due,2Q15_paid,2Q15_diff

1,100000,50000,50000,50000,20000,30000,50000,30000,20000

2,200000,190000,10000,100000,100000,0,100000,90000,10000

3,150000,200000,-50000,50000,100000,-50000,100000,100000,0

];

LOAD ID_company,Data, SubField(QuarterPaidType,'_',1) as Quarter, SubField(QuarterPaidType,'_',2) as PaidType;

LOAD ID_company, Period, Data, if(WildMatch(Period,'*Q*'),Period) as QuarterPaidType Resident Temp;

DROP Table Temp;

Dim:

ID_company

Quarter

Exp:

=Sum({<PaidType = {'Due'}>}Data)

=Sum({<PaidType = {'Paid'}>}Data)

=Sum({<PaidType = {'Diff'}>}Data)

[#6]

In the end I decided to split the result into several tables (due, paid), so your solution is used, thanks! The only thing I didn't understand is why you set 4 qualifier columns in your example...