Transpose

[rajeshvaswani77]

Hi Community,

I have a table as below in excel

Quarter	1995		1996
	# of Deals	Amount Invested	# of Deals	Amount Invested
1	503	$1,690,040,500	584	$2,426,059,900
2	466	$2,546,374,500	674	$3,113,011,000
3	433	$1,705,754,500	602	$2,597,614,300
4	494	$2,072,604,900	776	$3,208,942,800

I want to extract the Quarter, Year, NO. of Deals and Amount Invested.

How should I go about it?

thanks,

Rajesh Vaswani

[jagan]

Hi Rajesh,

Some how managed to get Quarter, NO. of Deals and Amount Invested using below script.  Will give a try and get the year also.

Temp:
CrossTable([# of Deals], Data)
LOAD F1,
     [# of Deals],
     [Amount Invested],
     [# of Deals1],
     [Amount Invested1]
FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Data:
LOAD
*
WHERE Not IsNull(NumberofDeals);
LOAD
Quarter,
AmountInvested,
Previous(NumberofDeals) AS NumberofDeals;
LOAD
F1 AS Quarter,
If([# of Deals] like '# of Deals*', Data) AS NumberofDeals,
If([# of Deals] like 'Amount Invested*', Data) AS AmountInvested
RESIDENT Temp;

DROP TABLE Temp;

Regards,

Jagan.

[tresesco]

Could you attach sample excel?

[#4]

Please refer the QVW and let me know you got your anwer or not.

[jagan · Accepted solution]

Hi Rajseh,

Try the final script

CrossTableTemp:

CrossTable(Quarter, Data, 2)
LOAD Quarter As Year,
F2 as Particulars,
[1.000000],
[2.000000],
[3.000000],
[4.000000]
FROM
[data.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Transpose()
));

DataTemp:
LOAD
Year,
Quarter,
If(Particulars = '# of Deals', Data) AS NumberOfDeals,
If(Particulars = 'Amount Invested', Data) AS AmountInvested;
LOAD
If(Len(Trim(Year)) = 0, Peek(Year), Year) AS Year,
Particulars,
Quarter,
Data
RESIDENT CrossTableTemp;

FinalData:
NoConcatenate
LOAD
Year,
Quarter,
NumberOfDeals
RESIDENT DataTemp
WHERE Not IsNull(NumberOfDeals);

INNER JOIN (FinalData)
LOAD
Year,
Quarter,
AmountInvested
RESIDENT DataTemp
WHERE Not IsNull(AmountInvested);

DROP TABLE CrossTableTemp, DataTemp;

Regards,

Jagan.

[Gysbert_Wassenaar]

See multi_header_pivot_import.qvw