6 Replies Latest reply on Dec 3, 2018 12:51 PM by Allison Mayer

# How to transform a one-to-many relationship into a one-to-one relationship based on value?

I am trying to create a calculated field to do the following:

I have hosts with multiple IP addresses related to them.  What I need to do is create a statement that says: if any of the statuses contain Y then change all of the statuses to Y, sum the count of IP addresses, and group by host name.  Basically, if any of the statuses on the host are Y, then they are all Y.

What I have:

HostnameStatusCount of IP
Host1Y7
Maybe2
N3
Host2Y8
N9
Host3N8
Maybe5
Host4Y

10

Host5Y

2

N3

What I need:

Hostname
Status
Count of IP
Host1Y12
Host2Y17
Host3N8
Maybe5
Host4Y10
Host5Y5

I think that I will need to use the fixed level of detail function, but I am not sure how to group Hostnames based on the value in the Status column.

• ###### 1. Re: How to transform a one-to-many relationship into a one-to-one relationship based on value?

{ FIXED [Host] : SUM( IF [Status] = "Y" then 1 else 0 END) }

This will tell you how many rows under a give host have "Y".

If greater than 0, you have at least 1.

Then:

{ FIXED [Host] : COUNT(  IF [The previous FIXED calc] > 0 then [IP Address] END) }

That will count all IP Addresses under that host because the first fixed calc will be on all rows for that host.

• ###### 2. Re: How to transform a one-to-many relationship into a one-to-one relationship based on value?

PS:  I don't get what the logic is for Host 3.  Based on your description I would expect it to be zero.

• ###### 3. Re: How to transform a one-to-many relationship into a one-to-one relationship based on value?

Hi,

Here it iS:

Thanks

Deepak

If it Helps, Pl Mark it Helpful and CORRECT to Close Thread

• ###### 4. Re: How to transform a one-to-many relationship into a one-to-one relationship based on value?

Joe,

I believe Allison wants the status column to be changed to 'Y' only for the Hosts that has at least one status as 'Y'  and rest should stay as is.

@Allisson

I hope this would work

Cal 1:

{Fixed [Host]: MAX(If [Status]='Y' then 'Y')}

New Status calculation: IF [Cal 1]='Y' then [Cal 1] Else [Status] END.

With new Status, IP Count would group automatically, we don't have to make any changes to that.

• ###### 5. Re: How to transform a one-to-many relationship into a one-to-one relationship based on value?

FYI,