Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
scotthan
Contributor III
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
Partner

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

I would do it the way Digvijay suggested above.

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