topic Need Script help! in QlikView

Need Script help!

Anonymous — Thu, 15 Dec 2011 21:03:27 GMT

Hello everybody!

I need help to read in a Movex table through ODBC connection with exchange rates uppdated daily.

field1: Currency (EUR,USD,CAD,CHY.....)

field2: Rate (for each currency)

field3: Date

field4: Changenr (if the rate changes more than ones a day this number increases, so it must be the max value)

How do i write the script so that only the diffrent currencies is displayed with the right rate from the last daterecord of each currency?

(i only want one row for each currency with the latest rate).

Please dont answer in a qvw file, because i cant open it.

Thanks/Patric

Need Script help!

vgutkovsky — Thu, 15 Dec 2011 23:28:05 GMT

You could do something like this:

data:

LOAD

Currency,

Rate,

Date,

Changenr

SELECT ...

FROM ...

;

LEFT JOIN (data) LOAD

Currency,

date(max(Date)) as Date,

1 as #LastDateFlag

RESIDENT data

GROUP BY Currency

;

Then your chart would be:

Dimension: Currency, Date

Expression: only({<#LastDateFlag={1}>} Rate)

Regards,

Vlad

Need Script help!

Anonymous — Fri, 16 Dec 2011 09:20:45 GMT

Hello Vlad

I cant get it to work, i tried the script below but it wont give me a correct answer.

It gives me a new field with the last ratedate for each currancy and for every row in the database, but i only want one row for each currancy with the rate from the last inputdate and i need this to be done directly in the script, not in a chart with an expression.

Can this be done??

Lastrate:

LOAD

     CUCUCD,
     CUARAT,
     CUCUTD,
     CUCRTP;
SQL SELECT CUCUCD,
    CUCUTD,
    CUARAT,
    CUCRTP
FROM "CAO_PROD".MVXJDTA.CCURRA;
Left JOIN (Lastrate)
LOAD
     CUCUCD,
     max(Date(Date#([CUCUTD],'YYYYMMDD'),'YYYY-MM-DD')) as Date,
     1 as #LastDateFlagRESIDENT LastrateGROUP BY CUCUCD

""CUCUCD is Currency CUARAT is Rate CUCUTD is Date like(YYYYMMDD) and CUCRTP is Changenr.""

Need Script help!

anderslinden — Fri, 16 Dec 2011 10:33:02 GMT

Hi! This may not be the most beautiful solution but it seems to work. Note that my date format is not the same as yous so you might have to work with your date formatting to get the join to work properly.

OriginTable:

LOAD CUCUCD,
CUARAT,
CUCUDT,
CUCRTP
FROM
CurrencyTables.xlsx
(ooxml, embedded labels, table is Blad1)
;
FinalTable:
LOAD CUCUCD,
max(CUCUDT) as MaxCUCUDT
resident OriginTable
group by CUCUCD ;

left join (FinalTable)
load CUCUCD,
CUCUDT as MaxCUCUDT,
max(CUCRTP) as MaxCUCRTP
resident OriginTable
group by CUCUCD,CUCUDT ;

left join (FinalTable)
load CUCUCD,
CUCUDT as MaxCUCUDT,
CUCRTP as MaxCUCRTP,
CUARAT as LastRate
resident OriginTable
;
drop table OriginTable;

Regards, Anders

Need Script help!

Anonymous — Fri, 16 Dec 2011 12:45:13 GMT

Thank you Anders!

It works fine after formating the dates.

Now im a very happy guy.

Once again Thanks!!

Patric