topic Re: join tables with different dimensions in QlikView

join tables with different dimensions

Fri, 15 May 2015 14:12:14 GMT

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

Re: join tables with different dimensions

Gysbert_Wassenaar — Fri, 15 May 2015 15:37:23 GMT

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.

Re: join tables with different dimensions

Mon, 18 May 2015 11:03:44 GMT

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?

Re: join tables with different dimensions

crusader_ — Mon, 18 May 2015 11:22:27 GMT

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

Re: join tables with different dimensions

Peter_Cammaert — Mon, 18 May 2015 11:22:47 GMT

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

Re: join tables with different dimensions

Mon, 18 May 2015 12:21:34 GMT

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