topic Re: CASE880 How to write script for replace my rate table only have Rate = 1 and monthly date from in QlikView

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

paulyeo11 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Saravanan_Desingh — Sun, 31 May 2020 16:20:48 GMT

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.

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

Saravanan_Desingh — Sun, 31 May 2020 16:21:41 GMT

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

paulyeo11 — Mon, 01 Jun 2020 03:01:51 GMT

Hi Saran

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

Paul

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

Saravanan_Desingh — Mon, 01 Jun 2020 13:10:53 GMT

you welcome