topic Re: How to convert from vertical table data to horiontal table in QlikView

How to convert from vertical table data to horiontal table

Wed, 13 Jul 2011 10:56:52 GMT

How to convert from vertical table data to horiontal table

Miguel_Angel_Baeyens — Wed, 13 Jul 2011 11:01:20 GMT

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

Re: How to convert from vertical table data to horiontal table

Wed, 13 Jul 2011 11:02:43 GMT

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

Re: How to convert from vertical table data to horiontal table

Miguel_Angel_Baeyens — Wed, 13 Jul 2011 11:14:22 GMT

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

Re: How to convert from vertical table data to horiontal table

Wed, 13 Jul 2011 11:18:30 GMT

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

Re: How to convert from vertical table data to horiontal table

Miguel_Angel_Baeyens — Wed, 13 Jul 2011 11:21:49 GMT

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

How to convert from vertical table data to horiontal table

Wed, 13 Jul 2011 11:38:38 GMT

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?

Re: How to convert from vertical table data to horiontal table

Wed, 13 Jul 2011 12:06:41 GMT

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

Re: How to convert from vertical table data to horiontal table

shumailh — Wed, 13 Jul 2011 12:44:48 GMT

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