Issue with Unsupported varchar Data Type in Qlik Compose

[jessica_molinacalabrese]

Hi,

I am writing to report an issue I've encountered regarding Qlik Compose's compatibility with certain varchar data types in tables when the length of the varchar is not defined. After analyzing the table metadata, I've observed that when the fields CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH are set to -1 (as seen in the attached image), Compose seems to struggle with loading that particular column.

I'd like to provide some context and details about the situation:

1. Background: My data source is Azure, and the data warehouse project in Compose is connected to a Microsoft SQL Server.

2. Scenario: The issue arises when the varchar data type is either not defined due to table creation through a query (such as "SELECT * INTO...") or when it is defined as varchar(MAX).

3. Observation: Upon investigating, I found that Qlik Compose encounters difficulties when loading tables with varchar columns exhibiting the characteristics mentioned above.

Considering the circumstances, I have a few questions:

• Is this limitation a known behavior of Qlik Compose?
• Are there any workarounds or solutions you can suggest to overcome this issue?

I appreciate your assistance and guidance in resolving this matter. If you require any further information or context, please don't hesitate to reach out.

Thank you for your attention to this matter. I look forward to your response.

Best regards, 

Jessica.

[sureshkumar]

Hello @jessica_molinacalabrese 

Its limitation column with datatype VARCHAR(MAX) is not supported in Compose.

Could you please try below workaround

Run Replicate with a metadata run and let the table(s) get created. 

Change the nvarchar(max) columns to varcahr(8000) on the target.

Make sure task is set to truncate on full load and 'Delete old changes and store changes in existing change table' for change tables.

Start the task.

Check collum(s) appear in the compose table creation.

Let us know how it goes.

Regards,

Suresh

[narmad78]

I understand that sql is lacking an identical operator and is using the equal operator very opportunistically. Magic conversions of data types is one thing but just throwing away certain values in a string column is different. Even more so when the actual input is an integer. I wonder how many characters can be added in order to have it fail …