topic Re: Getting the values from another table in QlikView

Getting the values from another table

Anonymous — Tue, 26 Apr 2016 22:03:40 GMT

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?

Re: Getting the values from another table

swuehl — Tue, 26 Apr 2016 22:22:46 GMT

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

Re: Getting the values from another table

sunny_talwar — Tue, 26 Apr 2016 22:42:20 GMT

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

Re: Getting the values from another table

petter — Wed, 27 Apr 2016 06:35:36 GMT

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

Re: Getting the values from another table

Anonymous — Wed, 27 Apr 2016 17:50:14 GMT

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

Thank you!

Re: Getting the values from another table

Anonymous — Wed, 27 Apr 2016 17:55:08 GMT

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!

Re: Getting the values from another table

sunny_talwar — Wed, 27 Apr 2016 18:08:03 GMT

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

Re: Getting the values from another table

Anonymous — Wed, 27 Apr 2016 18:33:08 GMT

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

Re: Getting the values from another table

sunny_talwar — Wed, 27 Apr 2016 18:37:38 GMT

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'}>}

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

Re: Getting the values from another table

Anonymous — Wed, 27 Apr 2016 18:53:55 GMT

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

Re: Getting the values from another table

sunny_talwar — Wed, 27 Apr 2016 18:55:25 GMT

It does, and you might be able to remove some of the stuff, but I tried adding all the inner Aggr set analysis to the outer Aggr set analysis as well.