topic Re: How to transpose table in QlikView

How to transpose table

Wed, 21 Jan 2015 06:45:17 GMT

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

Re: How to transpose table

avinashelite — Wed, 21 Jan 2015 06:55:36 GMT

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

Re: How to transpose table

deepakqlikview_123 — Wed, 21 Jan 2015 06:56:29 GMT

You can use crosstable.

Crosstable (M/D/YY, Sales) Load * From … ;

Re: How to transpose table

its_anandrjs — Wed, 21 Jan 2015 06:56:40 GMT

Hi,

Here are your script

Directory;

LOAD Date,

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

Re: How to transpose table

its_anandrjs — Wed, 21 Jan 2015 07:01:28 GMT

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

Re: How to transpose table

Wed, 21 Jan 2015 07:02:04 GMT

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:

Usually the input data has only one column as qualifier field; as internal key (Product in the above example). But you can have several. If so, all qualifying fields must be listed before the attribute fields, and the third parameter to the Crosstable prefix must be used to define the number of qualifying fields.
It is not possible to have a preceding Load or a prefix in front of the Crosstable keyword. Auto-concatenate will however work.
The numeric interpretation will not work for the attribute fields. This means that if you have months as column headers, these will not be automatically interpreted. The work-around is to use the crosstable prefix to create a temporary table, and to run a second pass through it to make the interpretations:

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.

Re: How to transpose table

avinashelite — Wed, 21 Jan 2015 07:03:18 GMT

please find the attachment for the solution

Re: How to transpose table

sat_tok52 — Wed, 21 Jan 2015 07:15:07 GMT

Hi,

Use Cross Table to Transpose Table .See below Scree Shots .

-Sateesh

Re: How to transpose table

Wed, 21 Jan 2015 07:28:31 GMT

works great. thanks Anand.

Re: How to transpose table

avinashelite — Wed, 21 Jan 2015 07:29:13 GMT

Hi Jonathan,

Did it solved your problem???

If so please mark this as answered