Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a few columns of data that is just numbers and I want to get the sums before assigning them a name as the dataset currently does not have a name assigned to it.
eg. the table looks something like this
main:
A | B | C | D
1 | 1 | 2 | 3
5 | 1 | 2 | 3
1 | 2 | 2 | 5
7 | 1 | 3 | 3
there are other columns but are omitted in this.
I want to get the sum of one column (eg. A) and then assign the name "category A" to it and same for the rest; and put them all together in a table so it looks something like
Category | total sum
category A | 14
category B | 5
category C | 9
category D | 14
I tried something like:
table:
LOAD * inline [
"Category" | "total sum"
"category A" | sum(A)
"category B" | sum(B)
"category C" | sum(C)
"category D" | sum(D)
resident main] (delimiter is '|');
but it throws a garbage after statement error.
Is it possible to do this ?
I managed to figure out how to do it by referring to these resources;
basically, I used a cross table to change the column into a row value.
I had to do some manipulating of data but it managed to achieve what I intended.
May be as attached:
Main:
Load
Sum(A) as Category_A,
Sum(B) as Category_B,
Sum(C) as Category_C,
Sum(D) as Category_D;
Load * Inline [
A,B,C,D
1, 1, 2, 3
5, 1, 2, 3
1, 2, 2, 5
7, 1, 3, 3
];
Hi, May be try this
T1:
Load * Inline [
A,B,C,D
1, 1, 2, 3
5, 1, 2, 3
1, 2, 2, 5
7, 1, 3, 3
];
T2:
load
sum(A) as Category_A,
sum(B) as Category_B,
sum(C) as Category_C,
sum(D) as Category_D
resident T1;
I want to show them in a bar chart so that I can see the 4 items together.
This method works but gives me 4 different columns which the bar chart does not take in.
Is there a way such that I can show on the bar chart
- X axis shows category A, B, C ,D (which is the name that I assign)
- Y axis shows the numbers which are the sums.
I should add that those data for A, B, C, D is already loaded through a file so there is no need to inline load the data for A, B, C, D
I managed to figure out how to do it by referring to these resources;
basically, I used a cross table to change the column into a row value.
I had to do some manipulating of data but it managed to achieve what I intended.