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
