Data modelling question

[Anonymous]

Hi

I have 2 tables Summary Value table and Metric Values table

In Metrics Value table I have a field Escapes/Mo which has Actual value , and in Summary Value table I have 2 values related to Esacpes/Mo - Escapes_Per_Month_Bridge and Escapes_Per_Month_Target .. how do I get these 2 values into Metrics Value table ? These 2 are not joined tables.

Summary Values Table:

Metric Values Table

Thanks!

[sunny_talwar]

May be like this:

Table:

LOAD MetricName,

          CommonField,

          Actual

FROM Metric Values Table;

Left Join (Table)

LOAD CommonField,

          Metric Key as Bridge

FROM Summary Values Table

Where WildMatch(Metric Key, '*BRIDGE*');

Left Join (Table)

LOAD CommonField,

          Metric Key as Target

FROM Summary Values Table

Where WildMatch(Metric Key, '*TARGET*');

[Anonymous]

ok , what is common field here?

[sunny_talwar]

Something which determines what bridge value belong to which row. Something that connects the two tables

[swuehl]

Do you only have this one metric with these two summary values?

If not, is there a rule we can use to create MetricName values from Metric Key values?

Do you have already values in Bridge and Target columns (for other MetricName)?

In short, please elaborate a bit more on the tables we are looking at.

If it's only about these three lines, I would probably create a simple INLINE table in the script.

Or create a MAPPING table from the summary table and ApplyMap to the Metrics Value table.

[Anonymous]

actually the common field between these 2 tables is in a way MetricsKey and MetricsName , if you look at the values Escapes/Mo meaning Escapes Per Month .. I want the Bridge and Target values to be displayed in the same row for Escapes/Mo along with Actual value in Metric Values Table.. can we get that?

[sunny_talwar]

Would you be able to share some more data points for the common fields from both the tables. Just trying to see how to join them together for all matches (not just for this one)

[Anonymous]

Yes this is the only metric I want to get Bridge and Target values from Summary Values , and yes there are many values in Summary Values and Metric Values table .. with Actual,Bridge and Target values.

The user updates Summary Value table with Bridge and Target values timely .. so I cannot have inline table , how do I use apply map when there is no Common values between these 2 tables ... ? logically ESCAPES_PER_MONTH_BRIDGE and ESCAPES_PER_MONTH_TARGET values belong to Escapes/Mo .. but how do I tell Qlikview to join these 2 values to Escapes/MO ?

[swuehl · Accepted solution]

Something like (would be easier with some textual samples instead of screen shots )

Summary:

LOAD [Metric Key], Value

FROM SummaryTable;

Metrics:

LOAD MetricName,

          If(MetricName ='Escapes / Mo', Lookup('Value','Metric Key','ESCAPES_PER_MONTH_BRIDGE','Summary'), Bridge) as Bridge,

          If(MetricName ='Escapes / Mo', Lookup('Value','Metric Key','ESCAPES_PER_MONTH_TARGET','Summary'), Target) as Target,

          Actual

FROM MetricsTable;

[Anonymous]

Awesome ! Thank you!