Combine the output of Cube JS.

[alexeybahterwork]

Problem

I have a cube with two opposing dimensions, _contract_num and ds_number_. When I use these in the playground, the result is as shown in the screenshot(pic 1). Is it possible to combine different data? I understand that I can join the data, but in this case, all of the data is stored in a single table.

I need to establish a relationship between the _parent_guaranteeid and the _stringid in order to have the contract number supplied on the lines, as shown in the screenshot(pic 2).

_contract_num_ is output if sql: ${guarantee}.parent_guarantee_id is null

_ds_number_ is output if sql: ${guarantee}.parent_guarantee_id is not null

I think Cube.js might be able to do this, but I'm not sure. Can you please help me solve this problem?

Related Cube.js schema

cube(`guarantee`, {
  sql_table: `guarantee`,

  title: `Guarantee`,

  data_source: `default`,

  joins: {  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primary_key: true,
      title: `ID`,
      shown: false,
    },

    string_id: {
      sql: `id`,
      type: `string`,
      title: `string_id,
      shown: true,
    },

    parent_guarantee_id: {
      sql: `parent_guarantee_id`,
      type: `string`,
      title: 'parent_guarantee_id',
    },

    contract_num: {
      case: {
        when: [
          {
            sql: `${guarantee}.parent_guarantee_id is null`,
            label: { sql: `contract_num` },
          },
        ],
        else: { label: ` ` },
      },
      type: `string`,
      title: 'contract_num',
    },

    ds_number: {
      case: {
        when: [
          {
            sql: `${guarantee}.parent_guarantee_id is not null`,
            label: { sql: `contract_num` },
          },
        ],
        else: { label: ` ` },
      },
      type: `string`,
      title: 'ds_number,
    },

  },
});

I have simplified the cube, so I cannot provide the generated SQL. I believe it will not be necessary in this case.