Problem to filter registers in my script

[#1]

I'm starting developing in qlikview and i don't know how to filter registers in my script:

LOAD

  TEXT(INVENTTRANSID)   as INVENTTRANSID,

  TEXT(DIMENSION3_)      as PROYECT,

  TEXT(PURCHID) & '-' &  TEXT(LINENUM) as PURCHLINE,

  TEXT(PURCHPRICE)       as UNITPRICE,

  TEXT(CURRENCYCODE)     as COIN,

  DATE(CONFIRMEDDLV)     as CONFIRMEDDLV

FROM

[$(Ruta_QVD)\PURCHLINE.qvd] (qvd)

WHERE DATAAREAID = 'tase' ;

LOAD

  TEXT(CURRENCYCODE) as COIN,

  DATE(FROMDATE)    as CHANGE_DATE,

  NUM(EXCHRATE)          as CHANGE

FROM

[$(Ruta_QVD)\EXCHRATES.qvd] (qvd)

WHERE DATAAREAID = 'tase' ;

I only want the "CHANGE" that fulfill condition : the max "change_date" that is smaller than confirmeddlv. So I would get the right exchange but i don't know how to do it.

Thanks and sorry for my English.

[sujeetsingh]

Javier,

just what you do is in one load derieve a flag to present 1 where max "change_date" that is smaller than confirmeddlv else it should be 0.

Now do a resident load from this table and exclude those records where flag is 0.

Thanks

[#3]

Thanks a lot sujeetsingh,

I'm trying to do what you've said to me but i think i don't know to use correctly the max function. It returns "invalid expression".

The result should only return a value, the value of the currency in the nearest but not exceeding confirmeddlv. I think i'm not understanding you.

Thanks.

[sujeetsingh · Accepted solution]

Javier ,

you need to use group by first and then the max

[#5]

You're right sujeetsingh. Thanks a lot.

Regards