How to convert from vertical table data to horiontal table

[#1]

How to convert from vertical table data to horiontal table

[Miguel_Angel_Baeyens]

Hi and welcome to the QlikCommunity,

Can you please elaborate? What data are you using? Can you post a sample data?

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

How to convert the below table box report to horizontal rows.

EMP LOC SUM(SAL)

123  IND   2000

345 AUS  3000

675 NZ     3500

we are looking for the data likes

EMP            123  345  675

LOC             IND  AUD NZ

SUM(SAL) 2000 3000 3500

[Miguel_Angel_Baeyens]

Hi,

You can do it either in the load script or in the chart, depending on the other analyses and charts you need to display. Assuming this is your script

LOAD * INLINE [
EMP, LOC, SAL
123, IND, 2000
345, AUS, 3000
675, NZ, 3500
];

Create a new pivot table chart, set EMP and LOC as dimensions and

Sum(SAL)

as expression. Finish the chart and click and hold on the EMP column until you see a blue vertical arrow. Drag it to the top part of the chart, so the arrow changes to horizontal, then release the mouse. Do likewise with the LOC field.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#5]

is there any property for changing the vertical rows to horizontal rows? If have please tell me..

[Miguel_Angel_Baeyens]

Yes, you can change from vertical to horizontal as I mentioned above. Did you try it?

Another option is to create a straight table instead using dimensions and expressions as above, go to the chart properties and click on "Horizontal".

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#7]

Maybe Anand123 is looking for the "crosstable" function as the solution...?

At what stage are you trying to convert your data?  Is it the case that your source data (ie excel) stores all the values in columns, but you want to load it into QV as row entries?

[#8]

Thanks..

Report data source is excel,it contains 60000 records,if writes srcipt it is not a best practice..is any option to change from vertical data to horizontal rows like below...please tell me.

Sample table box output:

PS_No	ENAME	DEPT	LOC	SKILLS	DESGN	M_Name	SAL
296800	RAM	BI/DW	BANGALORE	COGNOS	SE	ROHIT	35000
296801	SITHA	BI/DW	BANGALORE	BO	SSE	ARUN	35000
296802	REKHA	BI/DW	BANGALORE	INFORMATICA	SE	VIDHYA	35000
296803	VANI	BI/DW	BANGALORE	DATASTAGE	SSE	KRISHNA	28000

Required output:

PS_No	296800	296801	296802	296803
ENAME	RAM	SITHA	REKHA	VANI
DEPT	BI/DW	BI/DW	BI/DW	BI/DW
LOC	BANGLORE	BANGLORE	BANGLORE	BANGLORE
SKILLS	Cognos	BO	INFORMATICA	DATASTAGE
DESGN	SSE	SE	SSE	SE
M_Name	ROHIT	ROHIT	ROHIT	ROHIT
SAL	35000	35000	35000	28000

[shumailh]

See attached file and Try this way:

CrossTable(ENAME, Data)
LOAD PS_No, 
ENAME, 
DEPT, 
LOC, 
SKILLS, 
DESGN, 
M_Name, 
SAL
FROM
C:\Sample\Sample1.xlsx
(ooxml, embedded labels, table is Data); 

Cheers

Shumail