"-" Dimension - How to show #NA instead of "-"

[profilejamesbond]

Hi,

I have two tables and both tables are associated based on table1.ID and Table2.ID

Table1

Table1.ID, Table1.Sales
1, 100 €
2, 200 €
3, 300 €

Table2

Table2.ID, Table2.Sales
1, 50 €
3, 400 €
4, 400 €

Now, I created table in Qlik SaaS Cloud.

Table1.ID   Table2.ID   Table1.Sales   Table2.Sales   Difference (Table2.Sales - Table1.Sales)
1     1    100 €     50 €      50 €
2     -     200 €     0 €       200 €
3     3    300 €     400 €    -100 €
-     4     0 €        400 €    -400 €

The problem is, "-" in the Table1.ID at row 4 and Table2.ID at row 2.

Reason is, both tables are associated based on ID while, in Table1 there is no data for ID = 4 while, same goes for Table2 there is no data for ID = 2. Following calculated dimension doesn't work because there is no value.

Dimension: If(IsNull(Table1.ID), '#NA', 'Table1.ID')

But, now I want to show "#NA" for ID = "-". How, to do it ?

Kind regards,

Ishfaque Ahmed

[BrunPierre]

Try 

=If(Len(Trim(Alt(Table1.ID, Table2.ID)))=0, '#NA', Alt(Table1.ID, Table2.ID))

[Chanty4u]

Try this 

=If(IsNull(Table1.ID) or Len(Table1.ID)=0,

    '#NA',

    Table1.ID

)

Or may be this 

=If(Len(Trim(Table1.ID))=0, '#NA', Table1.ID)

Or

Create calculated dimension 

=Alt(Table1.ID, '#NA')

Or 

If(IsNull(Table1.ID), '#NA', Table1.ID)

[profilejamesbond]

Hi @BrunPierre;

Hi @Chanty4u;

How your calculated dimensions / expressions will work ?

Do you think if there is no data then these expressions will work ?

Table 1 contains 1, 2, 3 IDs while, table 2 contain 1, 3, 4 IDs.

Thanks

[marcus_sommer]

Resolving any NULL stuff within the UI is the wrong place. In the majority of the scenarios it will be technically possible but it could cause huge pain in regard of the needed efforts + complexity as well as the disadvantages in the usability and performance. Therefore I suggest to do the essential work within the data-model.

This might be reached by using a star-scheme data-model which would mean to concatenate both tables into a single fact-table. It's officially recommended data-model with the best compromise to efforts, simplicity and performance.

In addition or as an alternatively the missing ID's on each table might be identified and populated with an own logic.

[profilejamesbond]

Thank you, @marcus_sommer for heading me to the right direction. I tuned advise as below:

I write this script and solved my problem. You can advise / add / remove best practices.

Noconcatenate

tbl_missing:

Load Key, x_status resident tbl1;

Join (tbl_missing) Load Key, y_status resident tbl2;

Drop Field x_status, y_status from tbl1, tbl2;

Nococatenate

Load Key,

If(Len(Trim(x_status)) <= 0, '#NA', x_status) as x_status,

If(Len(Trim(y_status)) <= 0, '#NA', y_status) as y_status,

From tbl_missing;

Drop Table tbl_missing;

I appreciate your feedback.

Thanks