4 Replies Latest reply: Mar 21, 2017 4:16 PM by Cassandra Baqir RSS

    Joins to show only()

    Cassandra Baqir

      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!

        • Re: Joins to show only()
          Muñoz Héctor

          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':

          18-03-2017 5-34-51.png


          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

            • Re: Joins to show only()
              Cassandra Baqir

              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?

              notes.JPG

              • Re: Joins to show only()
                Cassandra Baqir

                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?

                • Re: Joins to show only()
                  Cassandra Baqir

                  I joined on   TRX_DATE & '-' & EXP_TYPE_CODE & '-' & AU_NUM as KEY_DATE_EXP_AUbecause 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. !