MAX EMAIL ADDRESS in one row

rrr

How to display in one row the Work email (if exists) or home email if the work email is null?

this function does not work

MAX(CASE
WHEN "Person Email Addresses"."Email Type Code"='W1'
THEN "Person Email Addresses"."Email Address"
WHEN "Person Email Addresses"."Email Type Code"='H1'
THEN "Person Email Addresses"."Email Address"
END by "Worker"."Person Number")

Raghavendra Raghunath-Oracle

Hi @rrr You need to use a pivot view. Person Number will be in rows. Email Type will be in column. Email must be in Value column, On the Email, apply aggregation rule as Min or Max. You will get all Emails of a worker in a single row

Raghu

rrr

Hi @Raghavendra Raghunath-Oracle thank you for your answer.

But my final report should display 1 column for email:

If the employee have work email, Its displays work email

else, it displays home email.

and it should be use the max for 1 row for each emp.

Raghavendra Raghunath-Oracle

Hi @rrr unzip and unarchive the attached analysis to your instance. Run it and check. I have the logic modified in the Email column.

Raghu

Person Email.catalog.zip

rrr

@Raghavendra Raghunath-Oracle Thanks! its retrieves the right max Email.

But how can I display (marge) it into one Column ?

Raghavendra Raghunath-Oracle

Hi @rrr fyi

Raghu

Person Email Unique.catalog.zip