QV18 Duplicate values in dimension

Duplicate values in dimensions:




produce issue:
create table region (area varchar2(30), district varchar2(30), city varchar2(30));
insert into region values ('chandigarh', 'chandigarh', 'chandigarh');
insert into region values ('punjab', 'patiala', 'patiala');
insert into region values ('punjab', 'patiala', 'nabha');
insert into region values ('punjab', 'patiala', 'SAN');
insert into region values ('punjab', 'SAS', 'SAS');
insert into region values ('punjab', 'SAS', 'ZRK');

i added all column area , district, and city in dimension of PIVOT table
in city i have repeated values same reside in DISTRICT

and these repeated/duplicate values cause a bad result in the end.

The city in red box is extra and some time leads to trouble depend upon the expression. In simple words if i say, i dont want to see the repeated city name "patiala" in city column.


Where patiala is act as city as well as district.

We can fix this in two ways:
- at script level
- at chart level

AT SCRIPT level

at script level my table look like this

After modification in script it look like this:

use below script for above approach:
REG:
LOAD AREA,
    DISTRICT,
    if(CITY = DISTRICT,null(),CITY) as CITY;
SQL SELECT *
FROM SCOTT.REGION;

AT CHART level