Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
scotthan
Partner - Contributor III
Partner - Contributor III

"Lookups" - unusual set analysis question

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
]
;

5 Replies
maximiliano_vel
Partner - Creator III
Partner - Creator III

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
Partner - Contributor III
Partner - Contributor III
Author

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
MVP
MVP

You can do it like

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

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

jonathandienst
Partner - Champion III
Partner - Champion III

I would do it the way Digvijay suggested above.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein