topic Convert columns into rows while importing from excel?? in QlikView

Convert columns into rows while importing from excel??

Mon, 10 Oct 2011 10:29:59 GMT

Hello,

I have some data in excel as following

Project Profit Jan Feb March

abc 234 x x

xyz 3213 x x

.........

........

Now when i want to import this data into qlikview, I want these months to be in one single column so that i can show trend bases on these months. I dont want each month to appear as a seperate column in Qlikview

Can someone suggest how can i convert these months as a single dimension in the load script

Arif

Convert columns into rows while importing from excel??

kaushiknsolanki — Mon, 10 Oct 2011 10:32:43 GMT

HI,

When ever you import data from excel you will be prompted a wizard. If you go to next window of the wizrad you will find a crosstab feature. Clik on it.

Using these feature you can get the data of column in row format. For more look into help and search for cross tab.

Regards,

Kaushik Solanki

Re: Convert columns into rows while importing from excel??

Mon, 10 Oct 2011 10:40:44 GMT

Hi,

By using Cross Tab we can get that done..Attached sample appln..

-Jai

Convert columns into rows while importing from excel??

Mon, 10 Oct 2011 10:53:09 GMT

Thanks Jai,

This is exactly what i want. Can you explain it a bit

Thanks

Arif

Re: Convert columns into rows while importing from excel??

its_anandrjs — Mon, 10 Oct 2011 11:00:16 GMT

Hi Arif,

When you create a table like

CrossTable(Month, Value)

the first value column created with name Month and contains all first row data from excel and Value field when created it contains all the data which is related with months in your case and generate a column.

See the attached sample file

Rgds

Anand

Re: Convert columns into rows while importing from excel??

its_anandrjs — Mon, 10 Oct 2011 11:28:20 GMT

Hi,

For your example see the attached sample file.

Rgds

Anand

Convert columns into rows while importing from excel??

SunilChauhan — Mon, 10 Oct 2011 11:48:37 GMT

cross is the concept in which we can change the columns into rows

here we are defining data of same type into data and value

your example

Project Profit Jan Feb March

abc 234 x x

xyz 3213 x x

but after conversion into cross table it will be likee below

Project ,Profit,Month,data

in month Jan,feb ,mar will be covered where as valuess of those month are come under data

while using cross table we can write

cross table(Month,data,2)

Load

Project ,

Profit ,

;

from path

Convert columns into rows while importing from excel??

SunilChauhan — Mon, 10 Oct 2011 11:51:40 GMT

cross table(Month,data,2)

here 2 denote that no need to apply cross table on starting two field and apply on remaining .

Convert columns into rows while importing from excel??

Mon, 10 Oct 2011 11:55:09 GMT

Thank you everyone. That was really helpful.

Arif

Convert columns into rows while importing from excel??

Mon, 10 Oct 2011 11:58:57 GMT

One more question..Is there any way to denote that applying crosstable on last two or three fields is not needed? I mean if we have a long list of columns in excel and the months are somewhere in the middle?

Arif

Convert columns into rows while importing from excel??

its_anandrjs — Mon, 10 Oct 2011 12:04:02 GMT

Hi,

When ever you load data in cross table like CrossTable(Months, Value, 2) here 2 denotes after seconde column you want to make cross table suppose if you have 3 rows and 4 columns so your data loads becomes 12 and loaded 12 times your dable and here Months means ffrom which column in excel you want cross table and Values means it loaded all resting of same data in the column named Values.

As you say you want data in some middle so use to load data conditionally by using where clause.

Rgds

Anand

Convert columns into rows while importing from excel??

SunilChauhan — Mon, 10 Oct 2011 12:44:25 GMT

its depend upon u from where u want to make cross table.

but if you have data of same type then you can make cross table from starting.

other wise there is no other way.