topic Re: Rows to columns in QlikView

Rows to columns

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Rows to columns

sunny_talwar — Tue, 19 Apr 2016 19:33:13 GMT

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

];

Re: Rows to columns

Anonymous — Tue, 19 Apr 2016 19:35:36 GMT

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

Re: Rows to columns

Anonymous — Tue, 19 Apr 2016 19:51:12 GMT

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

sunny_talwar — Tue, 19 Apr 2016 20:43:08 GMT

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

Colin-Albert — Tue, 19 Apr 2016 20:47:09 GMT

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.

Re: Rows to columns

Anonymous — Tue, 19 Apr 2016 20:51:49 GMT

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

-----

68768

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

Right now, I am getting

ChargeUSD Jan 2016

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

4734 1

4579 1

....

...

Re: Rows to columns

Anonymous — Tue, 19 Apr 2016 20:53:17 GMT

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

Re: Rows to columns

Tue, 19 Apr 2016 21:04:43 GMT

Hi Colin,

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

Thanks,

Arsalan

Re: Rows to columns

Anonymous — Tue, 19 Apr 2016 21:09:45 GMT

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.

Re: Rows to columns

sunny_talwar — Tue, 19 Apr 2016 21:10:22 GMT

In the script or front end would also work?

Re: Rows to columns

sunny_talwar — Tue, 19 Apr 2016 21:11:05 GMT

Look at the attached for front end options

Re: Rows to columns

Anonymous — Tue, 19 Apr 2016 21:11:20 GMT

No Sunny! I am already doing this on my front end using the expressions. It is very simple at that level. But my requirement needs me to do this at the script level, which is kind of messy.

Re: Rows to columns

Colin-Albert — Wed, 20 Apr 2016 08:05:13 GMT

Is your chart a Straight Table chart?

Horizontal is on the Properties, Presentation screen.

Tick the box to transpose rows & columns.

Re: Rows to columns

Anonymous — Wed, 20 Apr 2016 13:51:52 GMT

Hi Sunny,

I am able to get the values in the front end already. But I need to achieve something for which, building this conversion in the script is important.

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.

Re: Rows to columns

Wed, 20 Apr 2016 14:01:16 GMT

Got it Colin, thanks!!!!!!