Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
];
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
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.
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
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.
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
....
...
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
Hi Colin,
Do we've a properties tab on the Straight tab? I can't seem to find it.
Thanks,
Arsalan
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.