topic Problem to filter registers in my script in QlikView

Problem to filter registers in my script

Wed, 04 Jun 2014 08:22:04 GMT

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.

Re: Problem to filter registers in my script

sujeetsingh — Wed, 04 Jun 2014 08:51:01 GMT

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

Re: Problem to filter registers in my script

Wed, 04 Jun 2014 09:12:00 GMT

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.

Re: Problem to filter registers in my script

sujeetsingh — Thu, 05 Jun 2014 04:27:05 GMT

Javier ,

you need to use group by first and then the max

Re: Problem to filter registers in my script

Fri, 06 Jun 2014 08:38:43 GMT

You're right sujeetsingh. Thanks a lot.

Regards