Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
cbaqirdhds
Contributor II

Joins to show only()

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!

Tags (1)
4 Replies
hector_munoz_in
Valued Contributor

Re: Joins to show only()

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

cbaqirdhds
Contributor II

Re: Joins to show only()

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

cbaqirdhds
Contributor II

Re: Joins to show only()

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?

cbaqirdhds
Contributor II

Re: Joins to show only()

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. !

Community Browser