Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

join tables with different dimensions

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






1 Solution

Accepted Solutions
crusader_
Partner - Specialist
Partner - Specialist

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

View solution in original post

5 Replies
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.


talk is cheap, supply exceeds demand
Not applicable
Author

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_
Partner - Specialist
Partner - Specialist

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
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

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