I have the following table:
+----------+----------+---------+----------+----------+----------+
| Date | A | B | C | P | D |
+----------+----------+---------+----------+----------+----------+
|2019-01-10| -111,000 | 666,000 | 78,000 | 45 | 120,000 |
|2019-01-09| 555,000 | 55,000 | 100,000 | 55 | 700,000 |
|2019-01-08| 48,000 | 30,000 | 40,000 | 65 | 450,000 |
|2019-01-07| 600,000 |-450,000 | -800,000 | 90 | 980,000 |
+----------+----------+---------+----------+----------+----------+
May I know how to create a temporary table from the existing table above to become the table as below:
+----------+----------+---------+----------+----------+
| Date |Components| Values | Comp_no | P |
+----------+----------+---------+----------+----------+
|2019-01-10| A |-111,000 | 1 | 45 |
|2019-01-10| B | 666,000 | 2 | 45 |
|2019-01-10| C | 78,000 | 3 | 45 |
+----------+----------+---------+----------+----------+
Then sort it according to absolute values as below:
- Give numbers to the largest value as '1', 2nd largest as '2' and so on.
The sorted_comp_no represents the order whereby the components is sorted where '1' is 'B', '2' is A and '3' is C.
+----------+----------+---------+----------+----------+--------------+
| Date |Components| Values | comp_no | P | sort_sequence|
+----------+----------+---------+----------+----------+--------------+
|2019-01-10| A |-111,000 | 1 | 45 | 2 |
|2019-01-10| B | 666,000 | 2 | 45 | 1 |
|2019-01-10| C | 78,000 | 3 | 45 | 3 |
+----------+----------+---------+----------+----------+--------------+
- Use the comp_no and sort.
The components are now sorted according to their values.
+----------+----------+---------+----------+----------+--------------+
| Date |Components| Values | comp_no | P |sorted_comp_no|
+----------+----------+---------+----------+----------+--------------+
|2019-01-10| B | 666,000 | 2 | 45 | 2 |
|2019-01-10| A |-111,000 | 1 | 45 | 1 |
|2019-01-10| C | 78,000 | 3 | 45 | 3 |
+----------+----------+---------+----------+----------+--------------+
I've already created the temporary table template but still stuck in taking the name of specific column only (A,B and C) to be put into a column of its own category ignoring column D . Column P is brought into the temporary table but remain as is. There is also a dependency to the date column as there are multiple data of different date.
But the end goal is to be able to "sort specific column according to its absolute value at a specific date dynamically".