Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

CASE880 How to write script for replace my rate table only have Rate = 1 and monthly date from 2000

Hi All

i Have monthly exchange rate table in excel as below , it is very simple the rate alway = 1 and date range from 2000 till 2020 with monthly data :-

Rate- date
1 - 1 January 2020
1 - 1 February 2020
1 - 1 March 2020
1 - 1 April 2020
1 - 1 May 2020

I have below load script that load the above raw data  :-

LOAD
'PM' AS SOURCE,
Rate,
MonthName(date) as [SalesMonth]
FROM Map_Rate_.xlsx
(ooxml, embedded labels, table is Sheet);

My question is how to replace the above exchange rate excel table with few line of script ? Since the excel table Rate alway = 1 from date 2000 till 2020.

Paul Yeo

1 Solution

Accepted Solutions
Saravanan_Desingh

Cal:
LOAD '1 - ' & Date(AddMonths(MonthStart(Today()),-IterNo()+1),'D MMMM YYYY') As [Rate - date]
AutoGenerate 1
While AddMonths(MonthStart(Today()),-IterNo()+1)>= '1/1/2000'
;

One solution is.

View solution in original post

4 Replies
Saravanan_Desingh

Cal:
LOAD '1 - ' & Date(AddMonths(MonthStart(Today()),-IterNo()+1),'D MMMM YYYY') As [Rate - date]
AutoGenerate 1
While AddMonths(MonthStart(Today()),-IterNo()+1)>= '1/1/2000'
;

One solution is.

Saravanan_Desingh

commQV83.PNG

paulyeo11
Master
Master
Author

Hi Saran

Thank you very much you help me save a lot of time.

Paul

Saravanan_Desingh

you welcome