LAC / Aggregating Strings in tables (find top/bottom columns)

Question & Answer
, ,
6

Hi @Filo, I think the straight forward solution it at the SQL layer:

WITH RankedShops AS (
    SELECT
        Region,
        Shop,
        Value,
        MAX(Value) OVER (PARTITION BY Region) AS Top_Value_per_Region,
        MIN(Value) OVER (PARTITION BY Region) AS Bottom_Value_per_Region,
        RANK() OVER (PARTITION BY Region ORDER BY Value DESC) AS Rank_Desc,
        RANK() OVER (PARTITION BY Region ORDER BY Value ASC) AS Rank_Asc
    FROM
        Source
)

SELECT
    Region,
    Shop,
    Value,
    Top_Value_per_Region,
    (SELECT Shop FROM RankedShops RS2 WHERE RS2.Region = RS1.Region AND RS2.Rank_Desc = 1) AS Top_Shop,
    Bottom_Value_per_Region,
    (SELECT Shop FROM RankedShops RS3 WHERE RS3.Region = RS1.Region AND RS3.Rank_Asc = 1) AS Bottom_Shop
FROM
    RankedShops RS1
ORDER BY
    Region,
    Shop;

Here are some more useful resources:

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!