Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all!
I am again struggling with new thing)))
I have historical currency data in this format:
1. table "history data" (downloaded from qvd file)
data, USD, EUR
data_1, value_USD_1,value_EUR_1
...
data_N, value_USD_N,value_EUR_N
2. table "current data" (API: XML format from http://www.cbr.ru/scripts/XML_daily.asp?date_req , without any specifications (just all current data)
CharCode (USD,EUR, etc), Value
for example:
USD, 50.55
EUR, 52.15
other_currency, value_other_currency
...
I want to join these two tables, using the table1's format. How make in easy?
I tried using this bunch of script:
ValCurs_temp:
LOAD
CharCode,
Value
FROM [lib://_____________________]
(XmlSimple, table is [ValCurs/Valute]);
LOAD
name,
%Key_ValCurs_27524C8C52D4E4A5
FROM [lib://_____________________]
(XmlSimple, table is ValCurs);
History_data:
LOAD
data,
USD,
EUR
FROM [lib://___________history.qvd] (qvd);
USD:
Load
date(today()) as data,
Value as USD
Resident ValCurs_temp
where CharCode = 'USD';
EUR:
Load
date(today()) as data,
Value as EUR
Resident ValCurs_temp
where CharCode = 'EUR';
kurs:
select *
Resident USD;
left join select EUR Resident EUR
where data.kursUSD = data.kursEUR;
drop table EUR;
drop table USD;
I know the problem is somewhere in italics)))
Hi,
Actually you don't even need to extract these two tables directly as you did.
Currency: //kurs
Load
date(today()) as data,
Value as USD
Resident ValCurs_temp
where match(CharCode,'USD'); //better use match() functions family when matching with characters
left join
Load
date(today()) as data,
Value as EUR
Resident ValCurs_temp
where match(CharCode,'EUR'); //better use match() functions family when matching with characters
That's all you need.
Regards,
Andrei
kurs:
select *
Resident USD;
left join select EUR Resident EUR
where data.kursUSD = data.kursEUR;
For starters you can't use Select to load data from resident tables. You must use Load.
Thank u very much, I tried to use LOAD, and also without "where data.kursUSD = data.kursEUR;" - i think its not impossible to use, but still it doesnt work properly( I cant see table "kurs" in data structure). Could you advise smth?
Hi,
Actually you don't even need to extract these two tables directly as you did.
Currency: //kurs
Load
date(today()) as data,
Value as USD
Resident ValCurs_temp
where match(CharCode,'USD'); //better use match() functions family when matching with characters
left join
Load
date(today()) as data,
Value as EUR
Resident ValCurs_temp
where match(CharCode,'EUR'); //better use match() functions family when matching with characters
That's all you need.
Regards,
Andrei
Maybe this (first drop everything after the LOAD of History_data):
LEFT JOIN (ValCurs_temp)
LOAD today() AS data, Value AS USD
RESIDENT ValCurs_temp
WHERE CharCode = 'USD';
CONCATENATE (History_data)
LOAD data, USD, Value AS EUR
RESIDENT ValCurs_temp
WHERE CharCode = 'EUR';
Then drop all tables you do not need anymore.
Note: I'm assuming that ValCurs_temp contains only one record for every currency.
Best,
Peter
thank you!!!!!!!!!!!