CODEWITHSUNDEEP
sqlexpressionconditional-formattingpalantir-foundryfoundry-contour
Turpan
Turpan

Reputation: 537

Conditional selection of data in palantir contour

I have two datasets with Client, 1st Nationality, 2nd Nationality, 3rd Nationality and with Country Code, Continent.

I would need an expresion using an expresion board in Palantir Contour that would select for me a client from the furthest contienet (in case the client has more than one nationality, it selects the farthest one assuming I am in America) when I make ulitimately a pilot table having only Continents and count of clients in it. If not directly palantir expression, also an SQL statement for this would be helpful. THank you

Sample example

enter image description here

Upvotes: 0

Views: 70

Answers (1)

Abdul Alim Shakir
Abdul Alim Shakir

Reputation: 1227

So we have to work on the following tables-input tables

To find the furthest continent for each client, our condition is America is the closest continent and Asia is the furthest continent after Europe. Try the following SQL code-

WITH Client_Continents AS (
    SELECT 
        cn.Client, 
        c.Continent,
        CASE 
            WHEN c.Continent = 'America' THEN 1  
            WHEN c.Continent = 'Europe' THEN 2  
            WHEN c.Continent = 'Asia' THEN 3    
            ELSE 0  
        END AS ContinentScore
    FROM Client_Nationality cn
    LEFT JOIN Continent c ON cn.First_Nationality = c.Country_Code
    
    UNION ALL
    
    SELECT 
        cn.Client, 
        c.Continent, 
        CASE 
            WHEN c.Continent = 'America' THEN 1
            WHEN c.Continent = 'Europe' THEN 2
            WHEN c.Continent = 'Asia' THEN 3
            ELSE 0
        END AS ContinentScore
    FROM Client_Nationality cn
    LEFT JOIN Continent c ON cn.Second_Nationality = c.Country_Code
    
    UNION ALL
    
    SELECT 
        cn.Client, 
        c.Continent, 
        CASE 
            WHEN c.Continent = 'America' THEN 1
            WHEN c.Continent = 'Europe' THEN 2
            WHEN c.Continent = 'Asia' THEN 3
            ELSE 0
        END AS ContinentScore
    FROM Client_Nationality cn
    LEFT JOIN Continent c ON cn.third_Nationality = c.Country_Code
),

Max_Continent AS (
    SELECT 
        Client, 
        MAX(ContinentScore) AS MaxContinentScore  
    FROM Client_Continents
    GROUP BY Client
)

SELECT 
    cc.Continent, 
    COUNT(DISTINCT cc.Client) AS ClientCount  
FROM Client_Continents cc
JOIN Max_Continent mc ON cc.Client = mc.Client
WHERE cc.ContinentScore = mc.MaxContinentScore  
GROUP BY cc.Continent
ORDER BY ClientCount;

Provides the result set -

result set

Upvotes: 0

Related Questions