creating a dimension based on measures

Hello

we have in one of our tables a bunch of metrics for our products. as of now each metric is named as prodX_measure1. which means that we have a bunch of different permutations of products and measures.

I want to create a new dimensions called products, which have all of our product values, and then have each measure as a “regular” measure.

image

Essentially populating the new dimension PROD by each unique prod derived from the different metric names we have setup. and then matching the specific products measure to its corresponding dimensional value and measure.

How do I go about doing this ?

regards
Alex

0 2 2,904
2 REPLIES 2

This is hard, in my mind, because the data you want to get at lies in the column names, not a value anywhere. I kept fumbling at a derived table or a case statement to transpose and transform the data but you can’t really do that if the names are where the information is…

Is there an information_schema type table you could use to reference the metadata and then do a CASE statement on in a derived table?

lizzy1
Participant II

If you have access to write a derived table in LookML you could use the SQL unpivot to get the table to look like this:
prod1_total | 100
prod1_ cost | 10
prod1_conversion | 10%
prod2_total | 150
prod2_cost | 13
prod2_conversion | 8%

From there you can write something like this:

select product_type,
       measure_type,
       any_value(measure) as measure
from (select left(measure,4) as product_type,
      right(measure,length(measure)-charindex('_', measure)) as measure_type,
      value
      from <table>) as a
group by 1, 2;

That would give you a table that looks like what you’re asking for then you can build on that new derived table instead.

Top Labels in this Space
Top Solution Authors