Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
drj12345
New Contributor III

Rows to columns

I have two Columns

Month                Price

-------                 ---------

Jan                    5

Feb                   4

Mar                  10

...

I want the rows to be columns and the output table should be as below:

Jan       Feb        Mar

---         ------       -------

5           4            10

what am I supposed to use? A cross table or Generic load? Please give me the load script for this conversion.

15 Replies

Re: Rows to columns

You need a Generic Load here

Something like this:

Table:

Generic

LOAD 1 as Field,

  Month(Date#(Month, 'MMM')) as Month,

  Price

Inline [

Month, Price

Jan, 5

Feb, 4

Mar, 10

];

drj12345
New Contributor III

Re: Rows to columns

I am doing this Sunny:

Infra_1:

Generic Load

Month, Price

Resident Table1

Group by Month,Price;

Is that right? I am getting this error Error: Aggregation expressions required by GROUP BY clause

drj12345
New Contributor III

Re: Rows to columns

Hi Sunny, am doing this:

Table:

Generic

LOAD 1 as Field,

  Month as [RMonth],

   Price

 

  resident Table1;

Since am getting the fields from already loaded fields, am doing a resident load. I see it is not the right way. What is the correct script in this regard?

The above load script is creating three different tables with Table.Jan 2016, Table.Feb 2016 and Table.Mar 2016 as Table names. This isn't right. Also, the Month already has values as Jan 2016, Feb 2016 and Mar 2016. So I don't think I need to format it as that's the right format.

Re: Rows to columns

You can create it like this:

Generic

LOAD 1 as Field,

  Month(Month) as [RMonth],

   Price

Resident Table1;

Generic creates multiple tables, but there are ways to combine them together again. Check this out: The Generic Load

The blog does mention about ways to combine it back together

Re: Rows to columns

No need to change your data.

Either use a Straight Table chart and check the Horizontal option on the Properties tab.

Or use a pivot table and drag the month dimension to the columns.

drj12345
New Contributor III

Re: Rows to columns

I already used the above script Sunny. That created 3 tables. The problem is, all the tables have their respect month Columns(Jan 2016, Feb 2016 and Mar 2016) and Column 'Field' with all values as 1. There is no ChargeUSD column in none of the three tables.

Instead, I changed it to

Generic

Price as Price,

  Month(Month) as [RMonth],

   1 as Field

Resident Table1;


This is showing up ChargeUSD as a column in all the tables. But I need the ChargeUSD values to be under respective month columns.

Expected View:


Table.Jan 2016:

Jan 2016

-----------


4734

4579


Table.Feb 2016:

Feb 2016

------

345

346346


Table.Mar 2016:


Mar 2016

-----

78

68768

68

--------------------------


Right now, I am getting


ChargeUSD      Jan 2016

----------------        ---------

4734                  1

4579                  1



....

...



drj12345
New Contributor III

Re: Rows to columns

Colin,

I am creating a straight table and for that, I need to create dimensions that are not present in any data. For that, I need to make these calculations within the script, not in the expression level or chart level.

Thanks

Not applicable

Re: Rows to columns

Hi Colin,

Do we've a properties tab on the Straight tab?  I can't seem to find it.

Thanks,

Arsalan

drj12345
New Contributor III

Re: Rows to columns

The Question is Pretty Straight.

I have the following columns in a table:

Month             Price

-------              --------

Jan 2016           50

Feb 2016          60

Mar 2016          34

...

...

-------------------------

I need the month rows to become columns as below:

Jan 2016      Feb 2016        Mar 2016

------------      --------------        --------------

50                    60                 34

The data is being loaded from existing table. So it is a resident load.

I just need to achieve this.