Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good Day,
I would like to ask for your help.
Is it possible to transpose this table
Date | 1/4/15 | 1/5/15 | 1/6/15 | 1/7/15 | 1/8/15 | 1/9/15 | 1/10/15 | 1/11/15 | 1/12/15 | 1/14/15 |
A | 1845 | 1685 | 1525 | 1290 | 955 | 1175 | 1145 | 1695 | 2100 | 2650 |
B | 3 | 3 | 3 | 1 | 0 | 1 | 1 | 3 | 3 | 3 |
C | 1626 | 1780 | 1967 | 775 | 0 | 851 | 873 | 1770 | 1429 | 1132 |
D | 2153 | 2228 | 2314 | 2469 | 2783 | 2562 | 2588 | 2223 | 2051 | 1885 |
E | 1614 | 1664 | 1721 | 1825 | 2034 | 1886 | 1904 | 1660 | 1546 | 1435 |
F | 536 | 536 | 536 | 536 | 536 | 536 | 536 | 536 | 536 | 536 |
into this kind of table
Date | A | B | C | D | E | F |
1/4/15 | 1845 | 3 | 1626 | 2153 | 1614 | 536 |
1/5/15 | 1685 | 3 | 1780 | 2228 | 1664 | 536 |
1/6/15 | 1525 | 3 | 1967 | 2314 | 1721 | 536 |
1/7/15 | 1290 | 1 | 775 | 2469 | 1825 | 536 |
1/8/15 | 955 | 0 | 0 | 2783 | 2034 | 536 |
1/9/15 | 1175 | 1 | 851 | 2562 | 1886 | 536 |
1/10/15 | 1145 | 1 | 873 | 2588 | 1904 | 536 |
1/11/15 | 1695 | 3 | 1770 | 2223 | 1660 | 536 |
1/12/15 | 2100 | 3 | 1429 | 2051 | 1546 | 536 |
1/14/15 | 2650 | 3 | 1132 | 1885 | 1435 | 536 |
please help me.
Thank you very much
Hi Jonathan,
It can be done in two steps.
1.In script use crosstable function and transpose it .
2.Using Pivot table you can achieve this format
You can use crosstable.
Crosstable (M/D/YY, Sales) Load * From … ;
Hi,
Here are your script
Directory;
LOAD Date,
A,
B,
C,
D,
E,
F
FROM
[..\..\Data\Book3.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Transpose()
));
1. You can do this in edit script and use transformation step
2. use Rotate
3. Transpose
Regards
Anand
Hi,
Better way is convert fields into number also which is numbers.
LOAD Date,
Num(A) as A,
Num(B) as B,
Num(C) as C,
Num(D) as D,
Num(E) as E,
Num(F) as F
FROM
[..\..\Data\Book3.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Transpose()
));
Note:- I load this data from excel file
Regards
Anand
Hello Jonathan,
Here you go with example.
There are a number of prefixes in QlikView, that help you load and transform data. One of them is the Crosstable transformation.
Whenever you have a crosstable of data, the Crosstable prefix can be used to transform the data and create the desired fields. A crosstable is basically a matrix where one of the fields is displayed vertically and another is displayed horizontally. In the input table below you have one column per month and one row per product.
But if you want to analyze this data, it is much easier to have all numbers in one field and all months in another, i.e. in a three-column table. It is not very practical to have one column per month, since you want to use Month as dimension and Sum(Sales) as measure.
Enter the Crosstable prefix.
It converts the data to a table with one column for Month and another for Sales. Another way to express it is to say that it takes field names and converts these to field values. If you compare it to the Generic prefix, you will find that they in principle are each other’s inverses.
The syntax is
Crosstable (Month, Sales) Load Product, [Jan 2014], [Feb 2014], [Mar 2014], … From … ;
There are however a couple of things worth noting:
tmpData:
Crosstable (MonthText, Sales)
Load Product, [Jan 2014], [Feb 2014], … From Data;
Final:
Load Product,
Date(Date#(MonthText,'MMM YYYY'),'MMM YYYY') as Month,
Sales
Resident tmpData;
Drop Table tmpData;
Finally, if your source is a crosstable and you also want to display the data as a crosstable, it might be tempting to load the data as it is, without any transformation.
I strongly recommend that you don’t. A crosstable transformation simplifies everything and you can still display your data as a crosstable using a standard pivot table.
please find the attachment for the solution
Hi,
Use Cross Table to Transpose Table .See below Scree Shots .
-Sateesh
works great. thanks Anand.
Hi Jonathan,
Did it solved your problem???
If so please mark this as answered