5 Replies Latest reply: May 18, 2015 8:21 AM by marisya chizhikova RSS

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