Conditional selection of data in palantir contour

Question

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

Please provide a minimal reproducible example with sample data and desired results. — Dale K
– Dale K, Commented Feb 5 at 15:28
Ciao Dale K, thanks for your comment, I have added as requested. — Turpan
– Turpan, Commented Feb 5 at 16:24

Abdul Alim Shakir · Accepted Answer · 2025-02-06 04:52:04Z

0

So we have to work on the following 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 -

answered Feb 6 at 4:52

Abdul Alim Shakir

1,3371 gold badge15 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Turpan Feb 13 at 16:30

Thank you, it seems like working :) But I got also a NULL category in the continent column, what data does it refer to?

Abdul Alim Shakir Feb 16 at 6:40

This will happen when none of the client nationality values match a valid country code in the continent table.

Turpan Feb 16 at 19:15

perfect, would you be able to such a way that could display also type of client please? I have two types, let's say A and B that is in a next column), so finaly the there would be under each continent Type A and its ClientCount and Type B and its ClientCount.

Abdul Alim Shakir Feb 17 at 3:51

You may keep the client type information in separate table. Then do join operation in the last select query and also group by client type.

Turpan Feb 17 at 5:14

I have actually client type information in the Client_Nationality table. Can I do it without join?

|

Collectives™ on Stack Overflow

Conditional selection of data in palantir contour

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related