Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
BI Consultant
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
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.
BI Consultant
is there any property for changing the vertical rows to horizontal rows? If have please tell me..
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.
BI Consultant
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?
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 |
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