Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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