Transpose columns into multiple rows

deona720
deona720 Member
in Magic ETL

I have this data set:

Member ID

23-Jan

23-Feb

23-Mar

23-Apr

22-Jan

22-Feb

22-Mar

22-Apr

123

33

41

23

23

32

32

34

34

456

4

15

15

15

6

6

2

2

789

2

4

8

8

8

4

8

12

111

24

24

26

26

27

22

27

27

222

3

12

12

13

14

14

12

12

333

2

1

1

444

2

2

2

5

6

8

10

11

The date columns represent the amount of logins to their account by month. I want to be able to consolidate this data into one column so that I can put this into cards more easily. I assume there would be \ 33 rows for member "123" that say Jan 23, and then 41 rows for the same member that show Feb 23, etc. This will be a monthly update, so the less manual labor the better.

How can I combine these columns into 1? Thanks in advance!

Best Answers

  • GrantSmith
    GrantSmith Coach
    edited January 24 Answer ✓

    You can utilize Magic ETL and the Dynamic Unpivot tile to take your columns and convert it to rows.

    Here's more information: https://domo-support.domo.com/s/article/360044951294?language=en_US

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    @deona720

    Just to be clear, using the Dynamic Unpivot tile in ETL will not quite get the results you described. You would end up with three columns:

    "MemberID" | "Date" | "Value"

    123 23-Jan 33

    You would not get 33 rows for member 123 in Jan.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Answers

Categories