OR condition from different data model in a dataset

Ask the Community
,
1

Hi!

I have a dataset, called purchase , which consists of two data models:

  • purchase_report.model
  • flag_must_have_item.model
    I’ve set a many-to-one relationship using product_code as join key in the dataset level.

I want to get number of purchases from purchase_report, where the purchased product have fresh_flag = ‘Fresh’ or flag_MHI = ‘MHI’ (must have item). But the issue is fresh_flag field is available in purchase_report.model, whilst flag_MHI is in flag_must_have_item.model.

I’m aware that CONDITIONS in holistics dataset explorer can’t handle OR operation for multicolumn filtering. So I came out with using business calculation in condition, with these code:

case(
  when: or(purchase_report.fresh_flag == 'Fresh', flag_must_have_item.flag_MHI == 'MHI')
  , then: 1
  , else: 0
)

then I select equals to 1 for that business calculation condition.

Since I only select 1 field, which is only purchase_order_id (from purchase_report.model) and I count it, it expected to be only display 1 row - 1 column table. But the result is like this:

Screen Shot 2022-05-24 at 15.35.55
Screen Shot 2022-05-24 at 15.35.551926×858 51.7 KB

Turns out the result is table with 1 column and 2 rows like shown on the pic above. Apparently the executed query is came out like this:

#standardsql
WITH `flag_must_have_item` AS (
  #standardsql
  SELECT  
    *
  FROM `flag_must_have_item`
  WHERE active_end_date >= current_date('Asia/Jakarta')
)

SELECT
  `purchase_report`.`fresh_flag` AS `empr_nf_75a6bf`,
  `flag_must_have_item`.`flag_mhi` AS `tmbl_n_d2aaf5`,
  COUNT(`purchase_report`.`purchase_order_no`) AS `c_empr_cpon_ac6837`
FROM
  `purchase_report`
LEFT JOIN `flag_must_have_item` ON `purchase_report`.`product_code` = `flag_must_have_item`.`product_code`
GROUP BY
  1,
  2
ORDER BY
  3 DESC
LIMIT 100000

I don’t get it why it adds two preceding columns before my only desired count purchase_order_id column. I believe it must be has something to do with the business calculation filter/condition, but I have no idea why.

Any solutions to get me fix the table result so it only show count purchase_order_id column as I expected with such cross-model OR filtering?

Thanks
~ Tio

2

Hi there Tio,

Thanks for sharing your case with details!

This is indeed a current limitation of our Business Calculations, as it executes after measure thus makes your values not automatically grouped into one.
We understand such a behavior is unexpected, and this is something that we’re planning to improve in the future. My apologies for any inconvenience caused.

For a workaround, please try Custom Dimension (Add Custom Dimensions and Measures | Holistics Docs) instead.

Do let me know if it works for you, Tio.

All the best,

3

Hi Vu Duong!

I think it suppose to be not working too with the Custom Dimension, since the two metrics are from different data models. So I think I’m going to join the two source tables of that models in the query model level instead.

It will be nice for Holistics to have a cross-model custom dimension/measurement.

Thanks~

4

Hi again Tio,

Yeah I got your case here! Sorry for misunderstanding your question.
It’s true that currently both our Business Calculation and Custom Dimension do not support cross-model fields yet.

The best way I can think of for now, is to centralize these fields into one model using Transform Model (Creating Transform Model | Holistics Docs). After that, you can create your report from such a model, and create the ‘is_fresh_or_MHI’ dimension within the model’s code, or as a Custom dimension.

In the meantime, let me add “supporting cross-model in Custom dimension & Biz Calculation” to our product backlog & raise it with the team. Will let you know when there’s an update!

Please let me know if this helps. Cheers!

5

That definitely will be a great feature to have since I frequently encountered with the needs of operating two fields from different data models.

Thanks for the response!
Cheers :beers:

2 Likes