Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Currency

Hi guys im having a bit of a problem with a currency table,

Im trying to convert some values according to a currecy table i have, the problem is, the currency table is a daily table and in my Data model i only have Months and Year.

Now i wanted to use the first day of the month as the currency for that whole month, i have tried making a Join which is easy as i just extract the Month and Year from the Date on the currency file and use it to join the Month, Year and Currency coin(EUR, USD) etc. My only problem with this join is that im am going to have 31 different values for each month in case the month has 31 days.

The best i came up with is this,

LOAD 

     Date,

     left(right(Date(Date,'DD/MM/YYYY'),4),4) as YEAR,

     left(right(Date(Date,'DD/MM/YYYY'),7),2) as MONTH_NUM,

     Rate,

     [To] as CURRENCY

FROM

E:\Software\Qlickview\reports\currencies2.xls

(biff, embedded labels) where

left(right(Date(Date,'DD/MM/YYYY'),10),2)=1; //This Loads only the first day, but in case i only have the 2nd for that month then i loose the conversion for that month.

As you can see by my coment

This Loads only the first day, but in case i only have the 2nd for that month then i loose the conversion for that month.

And Optimally how would i make a new field with the converted Rate already?? as in

REVENUE(from the first table im loading) / Rate as EUR,

Many Thanks

1 Reply
Not applicable

Currency

if you want to associate the currency with the first day of the month then you can apply monthstart fuction to get the first day of that month.