Hi everyone,
I'm struggling with my workflow as I'm not an expert and couldn't find the good solution for my problem.
It's a kind of transposition, I'm not sure how to call it.
Here is my input data. I have a line identified by its KEYS, different dates and a few characteristics (data) for each date.
The type of characteristics are the same by column but the value can change each day.
Here is my example (maybe easier than my explanations) :
| KEY_1 | KEY_2 | Date | Characteristic 1 | Characteristic 2 | Characteristic 3 | Characteristic 4 | Characteristic 5 |
| AAA | AAA.1 | 30/11/2020 | a.1 | a.2 | a.3 | a.4 | a.5 |
| AAA | AAA.1 | 29/11/2020 | aa.1 | aa.2 | aa.3 | aa.4 | aa.5 |
| AAA | AAA.1 | 26/11/2020 | aaa.1 | aaa.2 | aaa.3 | aaa.4 | aaa.5 |
| AAA | AAA.1 | 24/11/2020 | aaaa.1 | aaaa.2 | aaaa.3 | aaaa.4 | aaaa.5 |
| BBB | BBB.1 | 23/11/2020 | b.1 | b.2 | b.3 | b.4 | b.5 |
| BBB | BBB.1 | 22/11/2020 | bb.1 | bb.2 | bb.3 | bb.4 | bb.5 |
| BBB | BBB.1 | 21/11/2020 | bbb.1 | bbb.2 | bbb.3 | bbb.4 | bbb.5 |
Here is what I want as output :
I want less columns and the type of characteristics (data) specified in each row and then the value of these characteristics but for each dates.
| KEY_1 | KEY_2 | Date | Characteristic | Value |
| AAA | AAA.1 | 30/11/2020 | Characteristic 1 | a.1 |
| AAA | AAA.1 | 29/11/2020 | Characteristic 1 | aa.1 |
| AAA | AAA.1 | 26/11/2020 | Characteristic 1 | aaa.1 |
| AAA | AAA.1 | 24/11/2020 | Characteristic 1 | aaaa.1 |
| AAA | AAA.1 | 30/11/2020 | Characteristic 2 | a.2 |
| AAA | AAA.1 | 29/11/2020 | Characteristic 2 | aa.2 |
| AAA | AAA.1 | 26/11/2020 | Characteristic 2 | aaa.2 |
| AAA | AAA.1 | 24/11/2020 | Characteristic 2 | aaaa.2 |
| AAA | AAA.1 | 30/11/2020 | Characteristic 3 | a.3 |
| AAA | AAA.1 | 29/11/2020 | Characteristic 3 | aa.3 |
| AAA | AAA.1 | 26/11/2020 | Characteristic 3 | aaa.3 |
| AAA | AAA.1 | 24/11/2020 | Characteristic 3 | aaaa.3 |
| AAA | AAA.1 | 30/11/2020 | Characteristic 4 | a.4 |
| AAA | AAA.1 | 29/11/2020 | Characteristic 4 | aa.4 |
| AAA | AAA.1 | 26/11/2020 | Characteristic 4 | aaa.4 |
| AAA | AAA.1 | 24/11/2020 | Characteristic 4 | aaaa.4 |
| AAA | AAA.1 | 30/11/2020 | Characteristic 5 | a.5 |
| AAA | AAA.1 | 29/11/2020 | Characteristic 5 | aa.5 |
| AAA | AAA.1 | 26/11/2020 | Characteristic 5 | aaa.5 |
| AAA | AAA.1 | 24/11/2020 | Characteristic 5 | aaaa.5 |
| BBB | BBB.1 | 29/11/2020 | Characteristic 1 | b.1 |
| BBB | BBB.1 | 26/11/2020 | Characteristic 1 | bb.1 |
| BBB | BBB.1 | 24/11/2020 | Characteristic 1 | bbb.1 |
| BBB | BBB.1 | 29/11/2020 | Characteristic 2 | b.2 |
| BBB | BBB.1 | 26/11/2020 | Characteristic 2 | bb.2 |
| BBB | BBB.1 | 24/11/2020 | Characteristic 2 | bbb.2 |
| BBB | BBB.1 | 29/11/2020 | Characteristic 3 | b.3 |
| BBB | BBB.1 | 26/11/2020 | Characteristic 3 | bb.3 |
| BBB | BBB.1 | 24/11/2020 | Characteristic 3 | bbb.3 |
| BBB | BBB.1 | 29/11/2020 | Characteristic 4 | b.4 |
| BBB | BBB.1 | 26/11/2020 | Characteristic 4 | bb.4 |
| BBB | BBB.1 | 24/11/2020 | Characteristic 4 | bbb.4 |
| BBB | BBB.1 | 29/11/2020 | Characteristic 5 | b.5 |
| BBB | BBB.1 | 26/11/2020 | Characteristic 5 | bb.5 |
| BBB | BBB.1 | 24/11/2020 | Characteristic 5 | bbb.5 |
I will really appreciate your help 🙂
The example is to illustrate I have nearly 2 million rows so I need this in a quite dynamic way and not manually.
I know it can be a bit confusing, I tried to be as clear as possible. If you need more information don't hesitate.
Thanks in advance.
Best regards,
Axis
