filter dimension by an another dimension

[hakim_ez]

Hi all,

How to resolve this use case.

I have two dimensions Y et Y and I want to create a new dimension Z who display columnA with the condition of  ${TABLE}.columnB=1.

Dimension: X{

type: string hidden: no 

sql: ${TABLE}.columnA;; }

Dimension: Y{

type: string hidden: no 

sql: ${TABLE}.columnB;; }

Thanks

[prabhakaran_mai · Accepted solution]

Technically speaking, you could use case statement

Dimension Z {
sql: case when ${Y} = 1 then ${X} end;;
}

This would be null for columnB<>1, if you don't want the nulls you could apply the report filter `Z is not null`.

Another option is to create a derived table with the condition in it's SQL. With this approach, you don't have to add a not null filter to the reports.

Thanks,

Prabha Arivalagan

[hakim_ez]

 @prabhakaran_mai  thanks the second option works very nice and it is more convenient.