Who is interpreting the functions when loading data in a Script?

[#1]

I will try to explain my question with an example:

If I create a text object with =Week('2011-01-05') I get 1 as result, which is correct according to ISO 8601 and to what is described in the QV Reference Manual (week number 1 is that containing the January 4th).

But now suppose that I’m loading a table from a file which is in another system (AS400 in this case):

SQL

SELECT

bla, bla, bla,

Week(date) as Myweek;

bla, bla, bla

FROM myas400file

When I look at the table I realize that the week has not been calculated according to the QV description of the week() function (now, week number 1 is that containing January 1st), so I guess that the other system has executed the function in the SQL instruction (which is logical).

How can I force that QV retrieves date and uses its week() function to calculate Myweek within the same load iteration?

Do I need to first load the table and then iterate over it to calculate Myweek using the QV function?

If so, Can someone give me a hint on the code?

Thanks.

[Miguel_Angel_Baeyens · Accepted solution]

HI Carlos,

Since you are not using an explicit LOAD statement, all the info you are loading into QlikView comes as it is returned by your driver. Indeed, all that SQL SELECT statement is not executed by QlikView, but by your driver. What you can do and is strongly recommended is to use that LOAD statement to control data, and leave the SQL statements as simple as possible

Table:
LOAD date, // the original field
     Week(Date) AS WeekField; // QlikView Week() function.
 SQL
SELECT date
FROM myas400file;

Who interprets functions then? In the LOAD part, QlikView, in the SQL SELECT part your driver or your database manager. Think of a CAST() function. Will it work? Yes, if you use it in the SQL part, but it will not in the LOAD part, because CAST() is not a function in QlikView.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

EDIT: There is an interesting discussion on the LOAD / SELECT in this message. There are some others in the QlikCommunity in the same sense.

[swuehl]

Hi,

you could use the QV functions in a preceding LOAD, like

LOAD *,

week(date) as myWeek;

SQL

SELECT

bla, bla, bla,

  date,

bla, bla, bla

FROM myas400file

Hope this helps,

Stefan

[#4]

Thanks to both.

Problem solved, appart from having understood what LOAD and SQL do.

I agree with you in regards of using always LOAD, regardless SQL itself doing the job.