Case 850 How to link my exchange rate field link to my sales table YearMonth field ?

[paulyeo11]

Hi All

Below script give me the result i want , see table 1 :-

// Part 1 load sales raw data 

sales:
LOAD
(year(today()) - year(Date(Date#([AR Invoice Date],'M/D/YYYY'), 'DD/MM/YYYY'))) * 12 + month(today()) - month(Date(Date#([AR Invoice Date],'M/D/YYYY'), 'DD/MM/YYYY')) + 1 as Month_n_SO,
'PM' as SOURCE,
[Customer Name] AS COMPANY,
Date(Date#([AR Invoice Date],'M/D/YYYY'), 'DD/MM/YYYY') as [date],
[Row Total (SGD)] as sales
FROM
$(vRAWPath)$(vFile200)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

// Part 2 Load TimeLine

tmp:
LOAD
min(date) AS MinDate,
max(date) AS MaxDate
RESIDENT sales;
MaxMinDate:
NOCONCATENATE LOAD
MIN(MinDate) AS MinDate,
MAX(MaxDate) AS MaxDate
RESIDENT tmp;
DROP TABLE tmp;
LET varMinDate = Num(Peek('MinDate'));
LET varMaxDate = Num(Peek('MaxDate'));
LET vToday = num(today());
DROP TABLE MaxMinDate;
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate,
date($(varMinDate) + rowno() - 1) AS D,
year($(varMinDate) + rowno() - 1) AS Y,
month($(varMinDate) + rowno() - 1) AS M,
date(monthstart($(varMinDate) + rowno() - 1), 'MMM-YYYY') AS MY
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

// Part 3 Load Master Calendar

MasterCalendar:
LOAD TempDate AS date,
day([TempDate]) as [day],
D AS link_Date,
D AS Date,
Dual(Date(Monthstart([TempDate]), 'YY MMM'), MonthStart(TempDate)) as YearMonth,
num(month([TempDate])) as [month],
AutoNumber(num(year(TempDate)) & num(month(TempDate), '00')) as MonthSeqNum,
Y AS year
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;

Table 1 Result is correct , because the sales amount is link to YearMonth field :-

Now i add exchange rate Table Concatenate with above sales Table  :-

// Part 4 Load Exchange Rate monthly Table.

Concatenate
LOAD SOURCE,
Currency,
Rate,
Date(date, 'D/M/YYYY') as date,
Rate_
FROM
Map_Rate_.xlsx
(ooxml, embedded labels, table is Sheet);

See the Table 2  :-

Notice that the Exchange rate table , i have date and source field , it should be able to link to sales table. and i use Concatenate to make sales table and exchange rate table into one table. But still it does not work. not sure where i go wrong ?

Hope some one can advise me.

Paul

[martinpohl]

Hi,

with your concatenate you add your datas to calendar, but there is no month or yearmonth in.

Why do you want to concatenate, why only link?

Is exchange rate date every day or only month values?

If date only drop line concatenate

otherwise create YearMonth in calendar (as dual, otherwise the values won't match)

regards

[paulyeo11]

Hi Sir

Thank you very much for your sharing .

I only need Monthly exchange rate . ( I will take monthly end exchange rate , as monthly exchange rate ).

When you said link my exchange table , do you mean I just need to remove concatenation command ?

I just try to remove the Concatenate , I notice that now the YearMonth field field link to Sales Table and Sales Amount , But The exchange rate YearMonth field still not able link with sales table .

Hope you can advise me how to link ?

Below my table model :-

Paul Yeo

[paulyeo11]

Hi All

Enclosed my QVW

Paul