Problem declaring a variable in script using LET

[#1]

Hi all

I have the following code in script:

BENCH_DTE:

LOAD BASE.DATE as DATE

resident BSE

WHERE

month(BSE.DATE) = 'Dec'

ORDER BY BSE.DATE ASC

;

Let vbench_dte2 = Fieldvalue('DATE',1);

LET vbench_dte = peek('DATE',0,'BENCH_DTE');

After stepping through the code it seems both variables are been declared as NULL.  There are 2 rows in table BENCH_DTE with the values 31/12/2013 & 31/12/2014.  What I am trying to do is to assign the minimum date, hence the reason for the ORDER BY statement i.e. I require 31/12/2013 to be assigned to the variables.  Is the above syntax correct for FIELDVALUE AND PEEK?

thanks in advance

[alexandros17]

Syntax is correct I think that the problem comes from the load:

BENCH_DTE:

LOAD BASE.DATE as DATE

resident BSE

WHERE

month(BSE.DATE) = 'Dec'

ORDER BY BSE.DATE ASC

Why do you use BSE. while after the LOAD you use BASE. ??? Shouldn't they be the same prefix?

Let me know

[anbu1984 · Accepted solution]

Is Qualify on before table BENCH_DTE table? If yes, then try

Let vbench_dte2 = Fieldvalue('BENCH_DTE.DATE',1);

[simospa]

Try this:

BENCH_DTE:

FIRST 1 LOAD BASE.DATE as DATE

resident BSE

WHERE

month(BSE.DATE) = 'Dec'

ORDER BY BSE.DATE ASC;

LET NumRows=1;

FOR i=1 to $(NumRows)

  LET vbench_dte2=FieldValue('DATE',$(i));

//do some stuff....

NEXT;

Pay attention: you can use FIRST 1 to select only 1 row if you order the data.

Simone.