I have this MAQL query where I am uses facts in di...
# gooddata-cloud
I have this MAQL query where I am uses facts in different datasets. They are joined by the ad_funnel label. Is there a way to make this MAQL work? I've lost the exact error and am having trouble reproducing it, but it said something about not being able to hold context of facts across two separate fact data sources.
SELECT SUM(SELECT CASE WHEN {label/ad_funnels.ad_funnel} = \"Branded Search/Google Shopping\" THEN {fact/ad_metrics_pivot.ui_purchase_value} ELSE {fact/sales_metrics_pivot.gross_sales} + {fact/shipping} + {fact/order_tax} END)\r\n"
Any advice here?
At first sight your metric logic seems to be OK, but you have to use β€œIN” instead of β€œ=” when specifying attribute values. I am not sure if this is the root cause of the error though, could you try fixing the syntax and send over here the new error if any?
hi @Moises Morales thank you, but I still get this error:
Copy code
Incomparable dimensionalities in a multi-argument node, dimensionality='[dataset/ad_metrics_pivot/0875584c222647ffa3bc520600dd032c, dataset/sales_metrics_pivot/0875584c222647ffa3bc520600dd032c]
I created an even simpler metric:
SELECT SUM({fact/sales_metrics_pivot.gross_sales} + {fact/ad_metrics_pivot.ui_purchase_value})
and I get this error when trying to create an insight:
Copy code
"reason": "Antichain='[dataset/ad_metrics_pivot/0875584c222647ffa3bc520600dd032c, dataset/sales_metrics_pivot/0875584c222647ffa3bc520600dd032c]' has no witness",
Is it impossible to combine facts across datasets?
The error indicates that the facts are incompatible in the context of the operation being performed. This relates to how the datasets are connected for which I recommend rechecking your LDM structure. We have this community article that provides some best practices and explains nicely the LDM basics: Logical Data Model - Basic Rules of Data Modelling | The GoodData Community. If you require futher help, please send over here or via DM a screenshot of your LDM showing the relevant datasets.
You may need to specify at which dimension you want to evaluate the CASE using BY rule and use aggregation inside THEN and ELSE. e.g.
Copy code
select sum( select case when {label/a}="a" then sum({fact/x}) else sum({fact/y}) end by {label/label/a} )
or not use CASE at all and use arithmetic instead e.g.
Copy code
select ifnull((select sum({fact/x}) where {label/a}="a"),0)+ifnull((select sum({fact/y}) where {label/a}<>"a"),0)
πŸ™Œ 1
@Jakub Sterba - your second solution worked for me, but not the first one. Thank you for your help both!