Convert columns into rows while importing from excel??

[#1]

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

[kaushiknsolanki]

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

[#3]

Hi,

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

-Jai

[#4]

Thanks Jai,

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

Thanks

Arif

[its_anandrjs]

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

[its_anandrjs]

Hi,

For your example see the attached sample file.

Rgds

Anand

[SunilChauhan · Accepted solution]

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

[SunilChauhan]

cross table(Month,data,2)

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

[#9]

Thank you everyone. That was really helpful.

Arif

[#10]

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