topic Re: Script Variable Usage in SQL Where Clause in QlikView

Script Variable Usage in SQL Where Clause

Fri, 05 Jun 2015 14:05:20 GMT

Hello,

I try to make an incremental load work by first loading the current data into FAC_OND_DELEN_X from qvd and then determine the latest record and store that into a variable Start_Key.

The I load the new records from the DB2 database except the variable Start_Key in the Where Clause doesn't work.

Who can help me?

FAC_OND_DELEN_X:

LOAD*

FROM $(PAD)FAC_OND_DELEN_INC.qvd (qvd);

LET Start_Key_Inc = Max(OND_KEY);

Concatenate

FAC_OND_DELEN_NEW:

LOAD *;

SQL SELECT *

FROM DWH.FAC_OND_DELEN

Where OND_ST In(1,6,7) And OND_NR_LEVERING <> 0 And OND_KEY > $(Start_Key_Inc); // Also Tried '$(Start_Key_Inc)'

Re: Script Variable Usage in SQL Where Clause

swuehl — Fri, 05 Jun 2015 14:11:04 GMT

I believe you need to use the Max() function within a LOAD statement.

Try like

FAC_OND_DELEN_X:

LOAD *

FROM $(PAD)FAC_OND_DELEN_INC.qvd (qvd);

MAX:

LOAD Max(OND_KEY) as Max

RESIDENT FAC_OND_DELEN_X;

LET Start_Key_Inc = Peek('Max', 0, 'MAX');

DROP TABLE MAX;

Concatenate

FAC_OND_DELEN_NEW:

LOAD *;

SQL SELECT *

FROM DWH.FAC_OND_DELEN

Where OND_ST In(1,6,7) And OND_NR_LEVERING <> 0 And OND_KEY > $(Start_Key_Inc); // Also Tried '$(Start_Key_Inc)'

Re: Script Variable Usage in SQL Where Clause

Joaquin_Lazaro — Fri, 05 Jun 2015 14:12:35 GMT

Hi Mvdvosse

This works

LET vEscheme = 'PROD';

LET vVariable = 'CompanyA'

SQL SELECT *

FROM $(vEscheme).Table

WHERE FIELD1 = $(vVariable)

AND DATE >= '2013/01/01';

Hope this helps you

Joaquín

Re: Script Variable Usage in SQL Where Clause

Fri, 05 Jun 2015 14:23:13 GMT

Hi Swuehl,

I don't realy see the difference. The variable has the right value. It is just that the database doesn't recognize the variable.

Thanx for suggesting though.

Michel

Re: Script Variable Usage in SQL Where Clause

Joaquin_Lazaro — Fri, 05 Jun 2015 14:34:47 GMT

Hi:

Just try

LET Start_Key_Inc = chr(39) & Max(OND_KEY) & chr(39);

LET Start_Key_Inc = chr(39) & Peek('Max', 0, 'MAX') & chr(39);

Regards,

Joaquín

Re: Script Variable Usage in SQL Where Clause

swuehl — Fri, 05 Jun 2015 14:45:02 GMT

Michel,

the variable expansion should happen before the SQL code is sent to the DB.

When you run the code in debug mode of the script editor, what is shown as expanded code?

What do you expect to see?

Re: Script Variable Usage in SQL Where Clause

Peter_Cammaert — Fri, 05 Jun 2015 14:50:47 GMT

Just a guess: maybe because OND_KEY contains non-numeric values? Meaning that you cannot compare them using a greater than operator?

There is also something wrong with your CONCATENATE LOAD syntax. The table name should either be eliminated (you're not creating a new table, you're just adding records to a table that already exists) or replaced by a table name to which you want to concatenate the new data, e.g.

CONCATENATE (FAC_OND_DELEN_X)

LOAD ...

Peter

Re: Script Variable Usage in SQL Where Clause

Peter_Cammaert — Fri, 05 Jun 2015 14:59:36 GMT

Or if you create a log file during script execution, you will get an expanded SELECT statement as well. Does it match your expectations?

Re: Script Variable Usage in SQL Where Clause

swuehl — Fri, 05 Jun 2015 15:05:50 GMT

Peter,

I think we can assume a numeric variable value because of the max() function usage.

Re: Script Variable Usage in SQL Where Clause

Peter_Cammaert — Fri, 05 Jun 2015 15:21:43 GMT

Well, I learned to never trust data quality on rumours alone... A simple confirmation would lay that one to rest. Let's call it "deduction by elimination".

That LET statement shouldn't work. Like you said, Max() must be used in a LOAD statement. That makes the situation a lot more awkward...

Peter

Re: Script Variable Usage in SQL Where Clause

Mon, 08 Jun 2015 14:42:09 GMT

Hi Swuehl,

I have put The Max function in a load statement and combined that with a peek function. After matching the notation of the date and adding the '' in the where clause it works like clockwork. Thanx!

FAC_OND_DELEN_X:

LOAD*

FROM $(PAD)FAC_OND_DELEN_INC.qvd (qvd);

MAXKEY:

LOAD

Date(Max(OND_PER)-1, 'DD/MM/YYYY') AS MAXDATE

Resident FAC_OND_DELEN_X;

LET Start_Key_Inc = Date(Peek('MAXDATE',0), 'DD/MM/YYYY');

DROP Table MAXKEY;

Concatenate

FAC_OND_DELEN_NEW:

LOAD *,

OND_YEAR & '_' & OND_OGM AS OND_YEAR_OG;

LOAD *,

Year(OND_PER) AS OND_YEAR,

If(Year(OND_PER) = 2015 AND OND_OG = 39, 13, OND_OG) AS OND_OGM;

SQL SELECT *

FROM DWH.FAC_OND_DELEN

Where OND_ST In(1,6,7) And OND_NR_LEVERING <> 0 And OND_PER > '$(Start_Key_Inc)';