nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

How do I show 1 row per employee based on ranked address type

Riti Malhotra

Some employees have multiple address types on file. I am trying to show 1 row per employee based on address type. I want it show home address (address rank 1) but if none exist then show Mailing Address (Address rank 2). I created a case statement to assign address rank.

thanks

OIG-Report.catalog

Find more posts tagged with

OTBI (Oracle Transactional Business Intelligence)

Accepted answers

Riti Malhotra

Solution: I used the RANK function to assign each address type a value of 1, 2, or 3. If a home address wasn’t available, the system automatically assigned the mailing address a rank of 1. Then I used the FILTER function to return only the entries with a rank of 1.

All comments

gclampitt

Use a pivot table - put say the unique address identifier as a column then the rest of the address details as a measure and set the aggregation to max.

Can then hide the address identifier if need too.

Riti Malhotra

thanks but I need it show only only 1 address even if two exist.

gclampitt

what are the rules for that then? which address do you want to see? any?
eg if want the latest record - then create report B which returns the address with the max date, then report A returns the address for this date by referencing report B.
or can use the RANK function to return the one you want.
There will be plenty of similar examples on this forum if you search for them.

Riti Malhotra

I didn't find anything which is why I posted.. I am looking to only show Home address but it it doesn't exist then show Mailing address

gclampitt

Within your report. Use union and subquery (query based upon results of another analysis)

Home address Query : filter Address type = Home
Union
Work address Query : filter Address type = Mailing
and Emp not in (Subquery where Address type = Home)

So 1st part returns everyone with Home Address and 2nd part people with Mailing address, who dont have a Home address

Riti Malhotra

thanks I was able to rank them and then filter. appreciate your response.

RVohra

Good to see your issue got resolved, appreciate if you can outline the steps or screenshot explaining the solution. It will help the community in case there is someone who might have same requirement.

Riti Malhotra