join tables with different dimensions

[#1]

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

[Gysbert_Wassenaar]

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.

[#3]

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?

[crusader_ · Accepted solution]

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

[Peter_Cammaert]

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

[#6]

thank you!!!!!!!!!!!