Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to convert from vertical table data to horiontal table

How to convert from vertical table data to horiontal table

8 Replies
Miguel_Angel_Baeyens
Not applicable

How to convert from vertical table data to horiontal table

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

Not applicable

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

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
Not applicable

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

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

Not applicable

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

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

Miguel_Angel_Baeyens
Not applicable

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

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

Not applicable

How to convert from vertical table data to horiontal table

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?

Not applicable

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

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_NoENAMEDEPTLOCSKILLSDESGNM_NameSAL
296800RAMBI/DWBANGALORECOGNOSSEROHIT35000
296801SITHABI/DWBANGALOREBOSSEARUN35000
296802REKHABI/DWBANGALOREINFORMATICASEVIDHYA35000
296803VANIBI/DWBANGALOREDATASTAGESSEKRISHNA

28000


Required output:

PS_No296800296801296802296803
ENAMERAMSITHAREKHAVANI
DEPTBI/DWBI/DWBI/DWBI/DW
LOCBANGLOREBANGLOREBANGLOREBANGLORE
SKILLSCognosBOINFORMATICADATASTAGE
DESGNSSESESSESE
M_NameROHITROHITROHITROHIT
SAL35000350003500028000
shumailh
Not applicable

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

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