Alter Column: file to convert type String to Date

verytiredgirl
verytiredgirl Member
in Magic ETL

My current dataset has these columns:

Jan 2024 | Feb 2024 | Mar 2024 | Apr 2024 ………. and so on. And I'd want my table to just have 2 columns: Date and Point

So I used Unpivot tile, but I am unsure how to label my the new Date columns (number 4), so it will turn into a date format using Alter Column tile afterward. Do I need to add the date in front? Like 31/01/2024?

image.png

Best Answer

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓

    Hi @verytiredgirl,

    In this case, after performing the Dynamic Unpivot, you'll need to spend a bit more time creating a formula tile to convert 'FEB POINTS' to '01 Feb 2024', 'MARCH POINTS' to '01 Mar 2024', and so on. Then, change the format of the 'Date' column to Date using a Formula or Alter Column tile.

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Answers

  • Manasi_Panov
    Manasi_Panov Contributor

    Hello @verytiredgirl,

    Why don't you try Dynamic Unpivot instead of Unpivot?

    If your data initially includes columns for Jan 2024, Feb 2024, Mar 2024, and Apr 2024, start by Add Constant tile, then perform a dynamic unpivot. Here's an example:

    Initial Dataset:

    image.png

    Magic ETL:

    image.png

    Add Constant tile:

    image.png

    Dynamic Unpivot tile:

    image.png

    Result:

    image.png

    Then you can remove the TEMP column and convert the Date column in any format you like.

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • verytiredgirl
    verytiredgirl Member

    I have 15 other columns that are not related to point, things like Customer_ID, city, email, etc and ultimately I want to pick a Customer and see their points overtime.

    I have just tried the Dynamic Unpivot Tile, adding all other columns that are not points to "Select which columns will not be pivoted", but it didn't solve the Date issue

    image.png

  • Manasi_Panov
    Manasi_Panov Contributor

    Hi @verytiredgirl,

    Could you please take a screenshot of your initial dataset columns?

    If you have other columns, then Add Constant is not needed. Just add all the columns you don't want to unpivot here:

    image.png

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • verytiredgirl
    verytiredgirl Member

    Hi, due to privacy reason, I can't share the screenshot, but that's what I did as commented above. I selected all the columns I don't want to unpivot into 1, 2 is Date, 3 is Point, the screenshot is the preview

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓

    Hi @verytiredgirl,

    In this case, after performing the Dynamic Unpivot, you'll need to spend a bit more time creating a formula tile to convert 'FEB POINTS' to '01 Feb 2024', 'MARCH POINTS' to '01 Mar 2024', and so on. Then, change the format of the 'Date' column to Date using a Formula or Alter Column tile.

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Categories