Joins to show only()

[cbaqir]

My dashboard has two tabs - one to show a high level summary of Capital and one for Operating. On the page, there is the following code to show the capital note:

=only({<[NOTE_TYPE] = {'TOP'},EXP_TYPE_CODE={'Capital'}>}NOTE)

This works correctly.

However, the same format does not work for Operating and I can't figure out why.

=only({<[NOTE_TYPE] = {'TOP'},EXP_TYPE_CODE={'Operating'}>}NOTE)

Please help!

Thanks!

[hector_munoz]

Hi Cassandra,

=only({<[NOTE_TYPE] = {'TOP'},EXP_TYPE_CODE={'Capital'}>}NOTE) works because you only have one value in NOTE field for the combination of [NOTE_TYPE] = 'TOP' and EXP_TYPE_CODE='Capital' values.

=only({<[NOTE_TYPE] = {'TOP'},EXP_TYPE_CODE={'Operating'}>}NOTE) does not work because for EXP_TYPE_CODE='Operating' there is not exist any record with [NOTE_TYPE] = 'TOP':

If you try e.g. =only({<[NOTE_TYPE] = {'RUNRATE'},EXP_TYPE_CODE={'Operating'}>}NOTE) it works as you only have one value in NOTE field for the combination of [NOTE_TYPE] = 'RUNRATE' and EXP_TYPE_CODE='Operating' values.

Regards,

H

[cbaqir]

I understand and appreciate your answer but when I pull the data via SQL from the data warehouse, it doesn't support the view from QV that there isn't one associated note for Operating and TOP. Any other ideas?

[cbaqir]

I figured out the issue just not how to overcome it. It works for Capital because the TOP level AU_NUM is listed as 100000 but not for Operating in which the AU_NUM would be blank.

In my load script, I created a key to join the Notes dimension table (DM_STAT_MOR_NOTE) to the APP_MOR Fact table:

   TRX_DATE & '-' & EXP_TYPE_CODE & '-' & AU_NUM as KEY_DATE_EXP_AU, 

While this is correct from the dimension table, it does not have a valid record in the APP table to join to. What can I do?

[cbaqir]

I joined on   TRX_DATE & '-' & EXP_TYPE_CODE & '-' & AU_NUM as KEY_DATE_EXP_AU,  because the MOR tab has notes required by AU for MTD, YTD, FY and Run Rate. However, when I do that, I cannot get the Operating Overview tab to correctly display the NOTE_TYPE='TOP' and EXP_TYPE_CODE='Operating' because there is no AU associated with it. If I join on  TRX_DATE & '-' & EXP_TYPE_CODE & as KEY_DATE_EXP,  then the Overview tabs work but the MOR tab doesn't. Help! New example attached. !