UDF (user-defined function) for qlik sense

simonaubert — Wed, 16 Dec 2020 14:12:55 GMT

Hello,

here the wikipedia explanation : https://en.wikipedia.org/wiki/User-defined_function

In relational database management systems, a user-defined function provides a mechanism for extending the functionality of the database server by adding a function, that can be evaluated in standard query language (usually SQL) statements. The SQL standard distinguishes between scalar and table functions. A scalar function returns only a single value (or NULL), whereas a table function returns a (relational) table comprising zero or more rows, each row with one or more columns.

User-defined functions in SQL are declared using the CREATE FUNCTION statement. For example, a function that converts Celsius to Fahrenheit might be declared like this:

CREATE FUNCTION dbo.CtoF(Celsius FLOAT)
  RETURNS FLOAT
  RETURN (Celsius * 1.8) + 32

Once created, a user-defined function may be used in expressions in SQL statements. For example, it can be invoked where most other intrinsic functions are allowed. This also includes SELECT statements, where the function can be used against data stored in tables in the database. Conceptually, the function is evaluated once per row in such usage. For example, assume a table named ELEMENTS, with a row for each known chemical element. The table has a column named BoilingPoint for the boiling point of that element, in Celsius.

The query

SELECT Name, CtoF(BoilingPoint)
  FROM Elements

would retrieve the name and the boiling point from each row. It invokes the CtoF user-defined function as declared above in order to convert the value in the column to a value in Fahrenheit.

Each user-defined function carries certain properties or characteristics. The SQL standard defines the following properties:

Language - defines the programming language in which the user-defined function is implemented; examples include SQL, C, C# and Java.
Parameter style - defines the conventions that are used to pass the function parameters and results between the implementation of the function and the database system (only applicable if language is not SQL).
Specific name - a name for the function that is unique within the database. Note that the function name does not have to be unique, considering overloaded functions. Some SQL implementations require that function names are unique within a database, and overloaded functions are not allowed.
Determinism - specifies whether the function is deterministic or not. The determinism characteristic has an influence on the query optimizer when compiling a SQL statement.
SQL-data access - tells the database management system whether the function contains no SQL statements (NO SQL), contains SQL statements but does not access any tables or views (CONTAINS SQL), reads data from tables or views (READS SQL DATA), or actually modifies data in the database (MODIFIES SQL DATA).

User-defined functions should not be confused with stored procedures. Stored procedures allow the user to group a set of SQL commands. A procedure can accept parameters and execute its SQL statements depending on those parameters. A procedure is not an expression and, thus, cannot be used like user-defined functions.

Some database management systems allow the creation of user defined functions in languages other than SQL. Microsoft SQL Server, for example, allows the user to use .NET languages including C# for this purpose. DB2 and Oracle support user-defined functions written in C or Java programming languages.

So, while Sub /Call logic is similar to stored procedure, it appears we don't have this UDF functionality in Qlik Sense,which would very useful to simply some script or expression in front.

Best regards,

Simon

Re: UDF (user-defined function) for qlik sense - Status changed to: Open - Collecting Feedback

Patric_Nordstrom — Mon, 22 Feb 2021 15:36:10 GMT

Thank you for your feedback on ways to improve our product. While this is something we understand would be useful, it's not on the short-term roadmap. Please continue to show your support for this idea.

Thanks,

Patric

Re: UDF (user-defined function) for qlik sense

DataWrangler — Fri, 13 Jan 2023 15:47:43 GMT

Hello @simonaubert and @Patric_Nordstrom

I believe that Simon's requirement for User Defined Functions (UDFs) can be met today in both Qlik View and QlikSense with parameterized variables.

While over 10 years old, the following article about UDFs by the QlikView Maven is still relevant and useful today:

QlikView Maven: Variable That Acts Like a User-Defined Function

Cheers!

Jeff R.

From now on, please track this idea from the Ideation por...

Meghann_MacDonald — Wed, 02 Aug 2023 16:41:14 GMT

From now on, please track this idea from the Ideation portal.

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.

Re: UDF (user-defined function) for qlik sense - Status changed to: Closed - Archived

Ideation — Wed, 02 Aug 2023 16:41:16 GMT

idea Re: UDF (user-defined function) for qlik sense - Status changed to: Open - Collecting Feedback in Suggest an Idea

UDF (user-defined function) for qlik sense

Re: UDF (user-defined function) for qlik sense - Status changed to: Open - Collecting Feedback

Re: UDF (user-defined function) for qlik sense

From now on, please track this idea from the Ideation por...

Re: UDF (user-defined function) for qlik sense - Status changed to: Closed - Archived