Hello, I have the following data (string) in my me...
# gd-beginners
Hello, I have the following data (string) in my metric “reason” :
Copy code
price, quality, design
design, quality
When displaying this in an insight, right now 3 rows would be displayed, each containing a big concatenated string. There would be 1 price, quality, design 1 price 1 design, quality What I want to have is 2 price 2 quality 2 design Any idea what I can do to make this work? Thanks !
Hi Paul, How does your metric work, please? Could you share with us the MAQL expression? Are you slicing the Insight anyhow?
Hi Ivana, Right now it’s really just simple insight, no fancy MAQL expression yet
Screenshot 2023-03-06 at 12.55.33.png
Hi Paul, this seems like a you have your data concatenated in a string format but want to treat them as separate values. GoodData will not be able to split it for you in a metric. But if you slightly modify your data model and split the data in your ETL (or a view) , you can hen use the native support for the many-to-many relation in GoodData like this: You can set the many-to-many relationship in the modeller by dragging the arrow in the model both ways. There are some rules and limitations but your case with “reasons” seems to be exactly the one for which the many-to-many (or M:N) relationships are intended. I do not know how exactly your current data model look like, but a simple model with customers, sales and purchase reasons (where each sales transaction can have multiple purchase reasons) would look like on the attached image. here the contents of the main tables would look like this (simplified): Sales purchase ID, Product, Customer, Revenue ID1, Product A, Customer X, $100 ID2, Product A, Customer X, $200 ID3, Product B, Customer Y, $1500 Purchase Reasons: Purchase Reason price design quality Sales Purchase Reasons Purchase ID, Purchase Reason ID1, price ID1, design ID1, quality ID2, price ID3, design ID3, quality Then with this setup you would be able to view (or filter) your sales (counts, amounts…) by the “Purchase Reason” dimension and it would work. NOTE: the first arrow goes from “Sales” to “Sales Purchase reasons” (so Sales Purchase reasons has a foreign key pointing to to Sales in it) and after that you just drag the arrow also the other way (no other foreign key is added). NOTE 2: please only use the M:N relation where it really makes sense - where there is a multi-value relationship, do not try to put them everywhere “just in case” Here is a documentation (for GD Platform) how it works and how to set it up: https://help.gooddata.com/doc/enterprise/en/data-integration/data-modeling-in-gooddata/many-to-many-in-logical-data-models/ NOTE 3: If you would have huge number of rows in Sales (and therefore also in Sales Purchase Reasons) tables and the join would take too much time, you can also consider this a bit more complex approach which reduces the number of rows needed: https://help.gooddata.com/doc/enterprise/en/data-integration/data-modeling-in-gooddat[…]ny-in-logical-data-models/many-to-many-modeling-performance/
🙌 1
thanks a lot Michael and Ivana ! I’ve forwarded this to our dev team and we’ll get back to you if we have further questions
👀 1
👌 2