Combining data into one pie chart

louiswatson
louiswatson Member
in Magic ETL

I'm looking to combine data from 6 sources into one pie chart. All data will be coming from Pipedrive but each dataset uses different filters.


I have setup an ETL which goes through each filter, I'm stuck on working out how to combine the data into two columns (stage and number of panels). Is there an optimal way to combine data into one pie chart?

Cheers

Best Answer

  • RobSomers
    RobSomers Coach
    edited September 2022 Answer ✓

    @louiswatson You can use the Add Constants tile in ETL. You'll add one tile per branch of your ETL and name the column the same for each one (Stage), and then for each constant, put the name of the stage that corresponds to the filters.

    Additionally, depending on your comfort level with CASE WHEN statements and the complexity of your filters and other tiles, you could do this without having to create different branches using a formula tile with a long case when formula.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

Answers

  • MichelleH
    MichelleH Coach

    @louiswatson How are your 6 data sources related? Without seeing your data and what you are trying to measure, you will likely need to use the Append Data tile in MagicETL.

  • louiswatson
    louiswatson Member

    They are all in a status from Pipedrive called 'Lost', I'd like to show a breakdown of the sites in lost. This is where the additional filters/date ranges differ from each dataset.

    All I need to do now is group the values into the filter groups below (tenure, instructed, no tenure, tenure no planning etc) and use the values for the pie chart. I wasn't sure how to group the data and combine together, the datasets are using 'No of panels' as the value. Hopefully this helps?

    Capture.PNG


  • mhouston
    mhouston Contributor

    @louiswatson are your stages mutually exclusive? if so, I think your next step will be a group by tile for each stage (group by stage and then count panels?) and then an append which will stack your 5 categories together.

    So i'd think your end dataset goal is:

    image.png

    which you'd then build your pie chart off... is this what you're aiming for?

  • louiswatson
    louiswatson Member

    This is what I'm aiming for! The stages are exclusive but they aren't named in Pipedrive, I wasn't sure how to create a stage name from filters in an ETL.

  • RobSomers
    RobSomers Coach
    edited September 2022 Answer ✓

    @louiswatson You can use the Add Constants tile in ETL. You'll add one tile per branch of your ETL and name the column the same for each one (Stage), and then for each constant, put the name of the stage that corresponds to the filters.

    Additionally, depending on your comfort level with CASE WHEN statements and the complexity of your filters and other tiles, you could do this without having to create different branches using a formula tile with a long case when formula.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • louiswatson
    louiswatson Member

    Thankyou all, really helpful! The 'add constants' tile in ETL did the trick.

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @louiswatson you could have significantly simplified your ETL by using a FORMULA tile with a CASE statement.


    The reason why this approach is cleaner is the CASE statement allows you to use an ELSE clause. When you split out your data using FILTER tiles as you have, if you miss a new filter value you exclude it from your output. Which is usually an accident.


    Also, by using a CASE statement the logic for all your new labels is gathered into one place instead of spread across several tiles.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories