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

[paulyeo11]

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

[Saravanan_Desingh · Accepted solution]

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]

[paulyeo11]

Hi Saran

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

Paul

[Saravanan_Desingh]

you welcome