Magic ETL

Magic ETL

Can I use a ETL or Beast mode to calculate starts and ends and show in a chart?

I have data showing the start and end date, such as the below. I want to use a chart to show starts & ends side by side by week. Is this doable with a ETL or beast mode?

CandidateName

CompanyName

StartDate

ActualEndDate

Name1

Company1

12/23/2024

Name2

Company2

12/23/2024

Name3

Company3

12/23/2024

Name4

Company4

12/23/2024

Name16

Company16

12/26/2024

Name17

Company17

12/26/2024

Name18

Company18

12/26/2024

Name19

Company19

12/26/2024

Name20

Company20

12/26/2024

Name21

Company21

12/26/2024

Name22

Company22

5/25/2024

12/22/2024

Name23

Company23

5/25/2024

12/22/2024

Name24

Company24

5/25/2024

12/22/2024

Name25

Company25

5/25/2024

12/22/2024

Name26

Company26

5/25/2024

12/22/2024

Name27

Company27

5/25/2024

12/22/2024

Name28

Company28

5/25/2024

12/22/2024

Name29

Company29

5/25/2024

12/22/2024

Name30

Company30

5/25/2024

12/22/2024

Name31

Company31

5/25/2024

12/22/2024

The chart should look something like the below.

Best Answer

  • Answer ✓

    Yes you can accomplish this using Magic ETL.


    Step 1: Split out your input data into two groups: Starts and Ends. Use two formula tiles.

    Formula tile #1 (Starts) will look like this:

    Formula tile #2 (Ends) will look like this:

    Step 2: Append these groups together using the Append Rows tile:

    Step 3: Use a filter tile to remove rows where the newly created `Date` field is null


    Step 4: Create a card that is the Grouped Bar chart type. Use the newly created Date field as the Category, a count of the candidate name (or combination (concatenated) name that is both candidate + company, or some other unique identifier as the Value. And the newly created Group field as the series and voila!

    Let me know if you get stuck or have any questions!

    If I solved your problem, please select "yes" above

Answers

  • Answer ✓

    Yes you can accomplish this using Magic ETL.


    Step 1: Split out your input data into two groups: Starts and Ends. Use two formula tiles.

    Formula tile #1 (Starts) will look like this:

    Formula tile #2 (Ends) will look like this:

    Step 2: Append these groups together using the Append Rows tile:

    Step 3: Use a filter tile to remove rows where the newly created `Date` field is null


    Step 4: Create a card that is the Grouped Bar chart type. Use the newly created Date field as the Category, a count of the candidate name (or combination (concatenated) name that is both candidate + company, or some other unique identifier as the Value. And the newly created Group field as the series and voila!

    Let me know if you get stuck or have any questions!

    If I solved your problem, please select "yes" above

  • Thank you! I was going down the wrong path trying to use Group By or Dynamic Pivot. Thank you!

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In