Incorrect syntax for parameters on custom fields in Qlik semantic models

Hi @andrew.gebhard 

We are able to reproduce this issue, it seems to be occurring because the field you are referencing to in the custom field exists in both the table that you're dragging the field from, but also in the table where you're trying to add the custom field. For example

If I remove the ModifiedDate field from the Currency table, I get the following result

Can you please confirm if this is also the case for you?

Hi Christiaan, 

Thanks for your answer. I will have time to test next week. In your example, are you adding the parameters from a table different than where you are adding the custom field? Would that work properly in any case, regardless of whether qualified/fully qualified is working? 

It's logical to me that TimeXtender would always want to fully-qualify fields that appear in multiple tables. But trying to load ‘ModifiedDate’ into a qlik table from a view where the field ‘ModifiedDate’ does not exist probably won't work either way… 

Kind regards,

Andrew

Hi @andrew.gebhard , yes I am adding the parameters from a table different than where you are adding the custom field. I am not sure I understand your second question above - “Would that work properly in any case, regardless of whether qualified/fully qualified is working”, could you please clarify? Please let me know how your test goes 

Hi @andrew.gebhard have you had a chance to test yet? 

Hi Christian,

Andrew is currently on vacation - so probably keep this on hold for a while...

Hi Christiaan, 

I can recreate the discrepancy you mention when a field with the same name is present in two tables. Then, TimeXtender is automatically fully qualifying the field (even when set to qualified). This is not entirely strange, since the field name is not unique:

However, this is not the problem I am trying to illustrate. When a field of the same name does not exist in both tables, the Qlik Sense script will fail on execution when there is a reference to a field from the other table. Let's consider an example below with the field ‘Project’ that only exists in the ‘Projects’ table. I will add it as a parameter in a custom field in the table ‘Databases’:

Now, we can look at the ‘Qlik Sense Enterprise Scripts’ that will be sent to Qlik Sense for this app. The views that TimeXtender creates (Qlik SQL View) remain the same and nothing is added for the ‘Project’ field in the ‘Databases’ table:

-- This code is made available exclusively as an integral part of
-- timeXtender. You may not make any other use of it and
-- you may not redistribute it without the written permission of
-- timeXtender a/s.

AS
SELECT
	[Database] AS [Database]
	,[DatabaseAlias] AS [DatabaseAlias]
	,[DatabaseDescription] AS [DatabaseDescription]
	,[DatabaseId] AS [DatabaseId]
	,[DatabaseType] AS [DatabaseType]
	,[ValidFrom] AS [ValidFrom]
	,[ValidTo] AS [ValidTo]
FROM [dbo].[Databases]
GO

However, TimeXtender adds the field to the Qlik Script:

"Databases":
LOAD
"Database", 
"DatabaseAlias", 
"DatabaseDescription", 
"DatabaseId", 
"DatabaseType", 
"ValidFrom" AS "Databases.ValidFrom", 
"ValidTo" AS "Databases.ValidTo", 
"Projects.Project" AS "Test";
SQL SELECT
"Database", 
"DatabaseAlias", 
"DatabaseDescription", 
"DatabaseId", 
"DatabaseType", 
"ValidFrom", 
"ValidTo"
FROM "TXDictionary_MDW"."dbo"."Test_Test_DatabasesDEV_SLQV";

The load of “Projects.Project” as “Test” will fail as the field does not exist in the SQL View and there is no join defined between the two tables. So, I do not believe that adding fields from another table as a parameter in a ‘Custom Field’ for a Qlik Endpoint is supported.

I.m.o it would be better to disallow references to other tables in Custom Fields on semantic layers.

Kind regards,

Andrew - Emergo

Hi @andrew.gebhard  your suggestion has now been implemented, please see the following item in the release notes for reference:

• SSL: Removed the option to include other tables and fields in a Custom Field script, than the table the field is being created on, as it didn't work and would just be empty anyway.

Hi Christian, 

I saw! Thanks for the heads up.

Kind regards,

Andrew