How to create a dynamic field based on the name of another field

Question & Answer
, ,
1

Hi all,

I have a table with a lot of different costs distributed on the rows, ex:

Dest_country | cost1 | cost2
DE |. x |. y

I would like in quicksight to create a field which groups multiple field names. something like:

if [costname| in (cost1, cost2 … ) then variable costs,
elseif … then fixed costs.

I can unpivot the table on SQL and create the new grouping column but this would result in >400M rows, and I don’t think that’s the best option to be handled in quicksight.

Any idea?

2

Hi @mattraga ,

It is not possible in QuickSight and as you said unpivot would provide you the results.

here is a simple example I tested of unpivot by creating a measures table (Athena SQL ) . Maybe using filters might reduce the amount of data to be queried .

create or replace view "unpivot_example" AS 
with sales as (
        select 100 as sales, 200 as profit , 'EMEA' as region, 1 as link
        union all
        select 200 as sales, 300 as profit , 'APAC' as region, 1 as link
        union all
        select 300 as sales , 400 as profit , 'APJ' as region, 1 as link
        union all
        select 200 as sales, 300 as profit , 'EMEA' as region, 1 as link
),
measures as (
    select 'sales' as measure , 1 as linka
    union all
    select 'profit' as measure, 1 as linka
        )

select s.region,s.sales,s.profit, m.measure, case when m.measure = 'sales' then sales else profit end as new_measure 
from sales s
join measures m on s.link = m.linka

QuickSight calculated field

ifelse
    (in
        (
            measure,["sales","profit"]),"revenue"
        ,"no groups"
    )

Screenshot 2023-02-22 at 11.59.28
Screenshot 2023-02-22 at 11.59.28844×388 20.9 KB

1 Like
3

Clear,
thank you for the SQL example.

1 Like