Hello - I need to know how to combine data that is similar to make one

lwinstead
lwinstead Member
in Datasets

I need to combine all the Loves, TAs etc so that I show one Loves or TA instead of each location.

image.png

Thanks in advance!

Answers

  • ArborRose
    ArborRose Coach
    edited June 24

    One way would be to use a beast mod calculation to create a column as a "reclass".

    CASE
    
  WHEN `Location` LIKE '%LOVES%' THEN 'LOVES'
    
  WHEN `Location` LIKE '%TA%' THEN 'TA'
    
  WHEN `Location` LIKE '%SPEEDWAY%' THEN 'SPEEDWAY'
    
  ELSE 'OTHER'
    
END

    Opps…I accidently entered before I was done.

    That would create a new row for each: LOVES, TA, SPEEDWAY, OTHER. Do that in a Magic ETL or as a formula on a card. Then aggregate (eg SUM, COUNT) the column to see how many you have, calculate averages, or whatever.

    Oh, and be careful with order of operation and searches like '%TA%'. Obviously you are going to have other descriptions that may have TA in them. So you may want to expand on that with case sensitive code check, or check for TA with a space following, or something to pull out the Travel Centers of America instead of something that just contains the string TA.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Categories