Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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

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 :-

RESULT BEFORE.png

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  :-

rate not link to yearmonth.png

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

3 Replies
martinpohl
Partner
Partner

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
Master
Master
Author

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 :-

table1.png

Paul Yeo


paulyeo11
Master
Master
Author

Hi All

Enclosed my QVW

Paul