Different profiling results when running column analyses with the Java and the SQL engines

All platform Talend Studio licenses with data quality.

You may get different results when you run column analysis with Week Frequency and Week Low Frequency indicators using the Java or the SQL engine.

This is due to the fact that the date function may differ between different database systems (DBMS) or even between different installations of the same DBMS system.

Let's take the WEEK(date [,mode]) function of MySQL as an example, for further information check Date and Time Functions . This function returns the week number for date . It takes a two-arguments form that enables you to specify whether:

• the week starts on Sunday or Monday,
• the return value should be in the range from 0 to 53 or from 1 to 53.

With the SQL engine, Talend Studio uses the function WEEK(date) . The mode argument is omitted and thus Talend Studio uses the default mode value as set in the DBMS configuration, in general mode=0 , but that depends on your MySQL installation.

However, if you need to change this default behavior, you can create an UDI (User Defined Indicator) where you specify the mode you want to use in the SQL query template.

With the Java engine, Talend Studio uses the parameter Locale.getDefault() to know about the above-listed two arguments and gets the results from the Java API. This means it uses the locale of Talend Studio and not the locale of the DBMS.

This explains why you may get different profiling result between the Java and the SQL engines on (Low) Week Frequency indicator and other date functions.