how can I exclude one min and one max in a dataset

WorldWarHulk
WorldWarHulk Member
in Magic ETL

basically, its a survey dataset that have a score of 1 to 10..

every quarter, we need to exclude 1 lowest and 1 highest.

currently we are doing the manual exclusion of the lowest and highest, we're wondering if we can do it in beastmode

Comments

  • NewsomSolutions
    NewsomSolutions Domo Employee

    I don't think you'd be able to do that in beastmode unless I'm not understanding something.  You'd have to do it in ETL. 

     

    Say MySQL, If the surveys come over with some ID you could do something like this...check my syntax and logic...but theory should be good. My thought here is you may have multiple surveys with the same lowest and highest value.

     

    transform 1 - determine the value

    select min(score) as `min score`, max(score) as `max score` from surveylist

     

    transform 2 - find low

    select top 1 `survey ID`

    from surveylist as l, transform1 t

    where l.score = t.minscore

     

    transform 3 - find high

    select top 1 `survey ID`

    from surveylist as l, transform1 t

    where l.score = t.highscore

     

    select * from surveylist

    where `survey ID` not in (select `survey ID` from transform2) -keeps 1 survey that had the lowest out

    and `survey ID`  not in (select `survey ID`  from transform3)-keeps 1 survey that had the highest out

     

    Thanks

    Matt

     

Categories