"Lookups" - unusual set analysis question

[scotthan]

Hi community,

The attached dataset is generated from two simple inline tables. The first is a list of hosts and attributes. The second is a relationship between hosts. I've copied the inlines below for ease of reference. What I am trying to do is create a chart consisting of Hostname, Target Hostname, and Target Hostname's Attribute. That last one does not actually exist in the dataset. To find it, we need to look up the Target Hostname in the Hostname field and find that host's corresponding attribute. For example, for Hostname "Host1", Target Hostname "Host2", the Target Hostname's Attribute should be "Att2".

The concept is simple enough but I can't figure out the expression. I have a list box next to the chart with what the answers should be. Please note that this is just a mock up. Due to reasons related to the real application, I cannot duplicate the data on the back end of the data model. Can anyone help? Hope the challenge is clear!

Hosts:

LOAD * INLINE [
    Hostname, Attribute
    Host1, Att1
    Host2, Att2
    Host3, Att3
    Host4, Att4
    Host5, Att5
];
Relationships:
LOAD * INLINE [
    Hostname, Target Hostname
    Host1, Host2
    Host1, Host3
    Host2, Host1
    Host3, Host1
    Host4, Host5
    Host5, Host4
];

[maximiliano_vel]

Try This

Hosts:

LOAD * INLINE [

    Target Hostname, Attribute

    Host1, Att1

    Host2, Att2

    Host3, Att3

    Host4, Att4

    Host5, Att5

];

Relationships:

LOAD * INLINE [

    Hostname, Target Hostname

    Host1, Host2

    Host1, Host3

    Host2, Host1

    Host3, Host1

    Host4, Host5

    Host5, Host4

];

And in table change

only(Attribute)

[scotthan]

Hi, please don't change the back end. This is a front end challenge.

[Digvijay_Singh]

If you can use Applymap, it will give required mapping, see if this can help..

Relationship:

Mapping Load Hostname, Attribute

Inline [

Hostname, Attribute

   Host1, Att1

    Host2, Att2

    Host3, Att3

    Host4, Att4

    Host5, Att5];

LOAD Hostname,

  ApplyMap('Relationship',Hostname,'Unknown') as Attribute,

  TargetHostname,

  ApplyMap('Relationship',TargetHostname,'Unknown') as TargetAttribute

   INLINE [

    Hostname, TargetHostname

    Host1, Host2

    Host1, Host3

    Host2, Host1

    Host3, Host1

    Host4, Host5

    Host5, Host4

];

[swuehl]

You can do it like

=FieldValue('Attribute',FieldIndex('Hostname',[Target Hostname]))

but this will only work when Attributes are unique to Hostnames.

[jonathandienst]

I would do it the way Digvijay suggested above.