Getting the values from another table

[Anonymous]

Hi

I have 2 tables say Table 1 and  Table 2 , I want to get Target and Bridge values from Table 2 without joining or concatenating in the data model .. my dimension for the chart is Category , so I need to find the value based on Category and User friendly metric name , match them and get the related Bridge and Target values .. how can I achieve that?

[swuehl]

These tables are unlinked?

Maybe like

=Only (Aggr( If( Category = [User Friendly Metric Name], [Target Value]), Category, [User Friendly Metric Name]))

=Only (Aggr( If( Category = [User Friendly Metric Name], [Bridge]), Category, [User Friendly Metric Name]))

[sunny_talwar]

May be if you can add a sample like before, it might be easier to help

[petter]

The straight forward and efficient solution in QlikView would be to create a key between the two tables. This is done in the Load Script by adding a field to each of the tables:

Table1:

LOAD

    AutoNumber( Category ) AS %CatMet,

    .....;

Table2:

LOAD

    AutoNumber( [User Friendly Metric Name] ) AS %CatMet,

  .....;

This will associate the two tables with each other so you can just refer to the two fields in the As Of chart without problem and without getting a cartesian join. This is an effective lookup.

A presumption I made is that the [User Friendly Metric Name] is a unique key in the Table2. If it is not... you will have to give more information to us about the nature of the the Table2...

[Anonymous]

Attached is my application Table 1 is weekly Metrics Table 2 is Metrics definition,And yes User Friendly Metric Name is Unique

Thank you!

[Anonymous]

The tables are linked but not directly , please take a look at the attached data model .. I just joined it on CLNDR_DT , hoping to get the Bridge and Target values .. it does not matter that both have to be joined as long as we get the needed values from Metrics Definition table.If there is a way I could get bridge and target values from Metrics Definitions tables without joining also please let me know.

Thank you!

[sunny_talwar]

See if attach works for you

Bridge Expression:

=Aggr(If(Category = [User Friendly Metric Name],

Only({<CLNDR_DT={'$(=vSelectedDate)'},[User Friendly Metric Name] ={'P0 Escapes','P1 Escapes','P2 Escapes','P3 Escapes'}>} [Bridge Value])), Category, [User Friendly Metric Name])

Traget Expression:

=Aggr(If(Category = [User Friendly Metric Name],

Only({<CLNDR_DT={'$(=vSelectedDate)'},[User Friendly Metric Name] ={'P0 Escapes','P1 Escapes','P2 Escapes','P3 Escapes'}>} TargetValue)), Category, [User Friendly Metric Name])

[Anonymous]

Yes this is it , but I have an open document trigger , where a User Friendly Metrics Name is selected , to avoid this for the bridge and target values i have ignored the userfriedly name but it does not seem to work, attached the app again , please take a look

[sunny_talwar · Accepted solution]

Try these:

Bridge:

=Only({<CLNDR_DT={'$(=vSelectedDate)'},[User Friendly Metric Name] ={'P0 Escapes','P1 Escapes','P2 Escapes','P3 Escapes'}>}

Aggr(If(Only({<[User Friendly Metric Name] = {'P0 Escapes','P1 Escapes','P2 Escapes','P3 Escapes'}>} Category) = Only({<[User Friendly Metric Name] = {'P0 Escapes','P1 Escapes','P2 Escapes','P3 Escapes'}>}[User Friendly Metric Name]),

Only({<CLNDR_DT={'$(=vSelectedDate)'},[User Friendly Metric Name] = {'P0 Escapes','P1 Escapes','P2 Escapes','P3 Escapes'}>} [Bridge Value])), Category, [User Friendly Metric Name]))

Target:

=Only({<CLNDR_DT={'$(=vSelectedDate)'},[User Friendly Metric Name] ={'P0 Escapes','P1 Escapes','P2 Escapes','P3 Escapes'}>}

Aggr(If(Only({<[User Friendly Metric Name] = {'P0 Escapes','P1 Escapes','P2 Escapes','P3 Escapes'}>} Category) = Only({<[User Friendly Metric Name] = {'P0 Escapes','P1 Escapes','P2 Escapes','P3 Escapes'}>}[User Friendly Metric Name]),

Only({<CLNDR_DT={'$(=vSelectedDate)'},[User Friendly Metric Name] = {'P0 Escapes','P1 Escapes','P2 Escapes','P3 Escapes'}>} TargetValue)), Category, [User Friendly Metric Name]))

[Anonymous]

That expression looks scary 🙂 .. Thank you very much it worked !