Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a currency table with QuarterStartDate,Rates , different currency codes.
QuarterStartDate | Rate | Currency |
3/1/2015 | 0.00636 | ADP |
3/1/2015 | 105.74 | EUR |
6/1/2015 | 0.00674 | ADP |
6/1/2015 | 112.17 | EUR |
9/1/2015 | 0.00679 | ADP |
9/1/2015 | 113.03 | EUR |
Now My requirement is I want to assign same Rate value from QuarterStartDate to QuarterEndDate by each Currency but we do not have QuarterEndDate.How do we create QuarterEndDate in data model and how can we assign same Rate value by each Currency from .QuarterStartDate to QuarterEndDate .
this is my expected output table:For example Currency is EUR
Dates | Rate | Currency |
---|---|---|
All Dates From 3/1/2015 - To 5/30/2015 | Same Rate Value for all these Dates 105.74 | EUR |
From 6/1/2015 To 08/31/2015 | Same Rate Value for all these Dates 112.17 | EUR |
From 9/1/2015 To 12/31/2015 | 113.03 | EUR |
Same logic for other Currency codes also |
Thanks in advance
Hi,
did you try with QuarterEnd()
Regards,
Try this one:
quarterend ( '2005-10-29' ) will return 2005-12-31
but in table we have QuarterStartdates only....we do not have all dates to generate QuarterEnd dates.
One way is to use the start date of the next period. Something like:
LOAD
QuarterStartDate,
If(Previous(Currency) = Currency,
Date(Previous(QuarterStartDate) - 1),
Today()) As QuarterEndDate,
Currency,
Rate
RESIDENT Currencies
ORDER BY Currency, QuarterStartDate DESC;
Now you can interval match the rates to the detail.
Hello Rejesh,
Try QuarterEnd(Date, 0,3) As QuarterEndDate.
So for eg QuarterEnd('06/01/2015', 0,3) it Returns 08/31/2015.
Please Try and let me know.
Thanks,
V.
Hi Rajesh,
Data:
LOAD * INLINE [
QuarterStartDate, Rate, Currency
3/1/2015, 0.00636, ADP
3/1/2015, 105.74, EUR
6/1/2015, 0.00674, ADP
6/1/2015, 112.17, EUR
9/1/2015, 0.00679, ADP
9/1/2015, 113.03, EUR
];
Final:
Load
'From ' & QuarterStartDate & ' To ' & Monthend(AddMonths(QuarterStartDate,2)) as Dates,
Rate,
Currency
Resident Data;
Drop Table Data;
Sample attached. Let me know.