Using constants with special characters in custom tables [Linux] [Windows]

Problem

In case backslash (\) has to be used as a part of the constant in custom table, it can be removed from the resulting values during the build. 

Let’s look into this issue using ‘N\A’ value as an example. In cases when you need to substitute specific values and use ‘N\A' for that, after the build, substituted values will be shown as ‘NA’:

Solution

The issue above arises from the fact that in MonetDB (which is used in Sisense), the backslash (\) character has specific handling rules, and its use in queries can lead to issues or unexpected behaviour due to how it's interpreted.

For example, if your data contains "N\A" but backslash isn't escaped properly, MonetDB may interpret \A as a control character or strip it during parsing. This could result in the string becoming just "NA".

In order to prevent such a situation from happening, you can choose from 2 alternative approaches:

1. The one we recommend is to use a forward slash (/) in custom queries, when a slash is necessary:
   
   
   
   [Alt Text: "Screenshot of a database query editor showing a SQL selection with columns labeled 'test1', 'test2', and 'test3', all displaying 'N/A' values."]
   
   
2. However, in cases when only backslash is acceptable, you may adjust your queries to escape the backslash character, so it’s not interpreted, but seen as a string, by MonetDB:
   
   

[ALT Text: SQL query with three columns labeled as test1, test2, and test3. Each displays 'N/A' in a parsed table preview, indicating no available data.]

Summary

Due to specifications of working with MonetDB, using forward slash (/) in custom queries is preferable, however, backslash (\) can also be used after query modification.

Disclaimer: This post outlines a potential custom workaround for a specific use case or provides instructions regarding a specific task. The solution may not work in all scenarios or Sisense versions, so we strongly recommend testing it in your environment before deployment. If you need further assistance with this, please let us know.