Alter Column: file to convert type String to Date
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?
Best 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.
0
Answers
-
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:
Magic ETL:
Add Constant tile:
Dynamic Unpivot tile:
Result:
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.
2 -
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 issue0 -
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:
If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.
1 -
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
0 -
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.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 744 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive