topic Re: Data modelling question in QlikView

Data modelling question

Anonymous — Thu, 05 May 2016 20:36:22 GMT

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!

Re: Data modelling question

sunny_talwar — Thu, 05 May 2016 20:40:24 GMT

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*');

Re: Data modelling question

Anonymous — Thu, 05 May 2016 20:42:51 GMT

ok , what is common field here?

Re: Data modelling question

sunny_talwar — Thu, 05 May 2016 20:44:09 GMT

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

Re: Data modelling question

swuehl — Thu, 05 May 2016 20:48:02 GMT

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.

Re: Data modelling question

Anonymous — Thu, 05 May 2016 20:49:22 GMT

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?

Re: Data modelling question

sunny_talwar — Thu, 05 May 2016 20:55:50 GMT

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)

Re: Data modelling question

Anonymous — Thu, 05 May 2016 21:02:28 GMT

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 ?

Re: Data modelling question

swuehl — Thu, 05 May 2016 21:23:32 GMT

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;

Re: Data modelling question

Anonymous — Thu, 05 May 2016 21:45:51 GMT

Awesome ! Thank you!