To homepage To homepage
Solved

Incorrect syntax for parameters on custom fields in Qlik semantic models

  • 26 April 2023
  • 7 replies
  • 82 views
andrew.gebhard
Rank
Userlevel 2
Badge +1

Hi team, 

TimeXtender allows adding parameters from a different table to a custom field in a semantic data model (Qlik). The resulting syntax/qlik script combination is always broken.

When using adding a custom field parameter from a different table, TimeXtender fully qualifies the Qlik syntax regardless of the settings. The resulting syntax on the Qlik side will no longer match the syntax in the views created by TimeXtender:

Qualified setting:

Fully qualified setting:

 

The resulting Qlik Script:

"Sales_Targets":
LOAD
"KPI", 
"Target", 
"DIM_Boekdatum.DayName" AS "Test";
SQL SELECT
"KPI", 
"Target"
FROM "Test"."dbo"."Test QVD_SLQV";

But the view has the following syntax:

CREATE VIEW [dbo].[Test QVD_SLQV]
-- Copyright 2011 timeXtender a/s
-- All rights reserved
--
-- 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
	[KPI] AS [KPI]
	,[Target] AS [Target]
FROM [dbo].[xxxxx]
GO

TimeXtender should either disallow adding fields from other tables as parameters in custom fields, or add the field on the frontend, similar to measures. Otherwise, timeXtender would have to add a join query to either the Qlik Sense load script or the SQL view. 

Kind regards,

Andrew - E-mergo 

icon

Best answer by Christian Hauggaard 1 August 2023, 11:22

View original

7 replies

Christian Hauggaard
Rank
Userlevel 6
Badge +5

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?

andrew.gebhard
Rank
Userlevel 2
Badge +1

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

Christian Hauggaard
Rank
Userlevel 6
Badge +5

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 

Christian Hauggaard
Rank
Userlevel 6
Badge +5

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

rory.smith
Rank
Userlevel 5
Badge +7

Hi Christian,

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

andrew.gebhard
Rank
Userlevel 2
Badge +1

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

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @andrew.gebhard 

You are correct, adding fields from another table as a parameter in a ‘Custom Field’ is currently not supported. Thank you for the feedback, I have passed on your suggestion to disallow references to other tables in Custom Fields on semantic layers to our Product team.

 

Reply


Terms and ConditionsCookie settings