Hi everybody,
I try to load currency data from National Bank of Poland. And this is my script:
LOAD @1, // this file contain names of currency tables for example a025z10020
date#(mid(@1,10,2)&'-'&mid(@1,8,2)&'-'&'20'&mid(@1,6,2),'DD-MM-YYYY') as WalutaData,
year(date#(mid(@1,10,2)&'-'&mid(@1,8,2)&'-'&'20'&mid(@1,6,2),'DD-MM-YYYY')) as WalutaRok,
month(date#(mid(@1,10,2)&'-'&mid(@1,8,2)&'-'&'20'&mid(@1,6,2),'DD-MM-YYYY')) as WalutaMiesiąc,
day(date#(mid(@1,10,2)&'-'&mid(@1,8,2)&'-'&'20'&mid(@1,6,2),'DD-MM-YYYY')) as WalutaDzień,
date(WeekEnd(date#(mid(@1,10,2)&'-'&mid(@1,8,2)&'-'&'20'&mid(@1,6,2),'DD-MM-YYYY')),'DD-MM-YYYY') as WalutaKoniecTygodnia
FROM
[http://nbp.pl/kursy/xml/dir.txt]
(txt, codepage is 1250, no labels, delimiter is '\t', msq)
Where (mid(@1,5,3)='z14' or mid(@1,5,3)='z13') and (left(@1,1)='a');
And now I need to declare values from table above. To dynamically load tables like this below:
// Start of [a025z100205.xml] LOAD statements
pozycja:
LOAD nazwa_waluty,
przelicznik,
kod_waluty,
kurs_sredni,
%Key_tabela_kursow_67171F56643FF090 // Key to parent table: tabela_kursow
FROM [http://www.nbp.pl/kursy/xml/a025z100205.xml] (XmlSimple, Table is [tabela_kursow/pozycja]);
tabela_kursow:
LOAD typ,
uid,
numer_tabeli,
data_publikacji,
%Key_tabela_kursow_67171F56643FF090 // Key for this table: tabela_kursow
FROM [http://www.nbp.pl/kursy/xml/a025z100205.xml] (XmlSimple, Table is [tabela_kursow]);
// End of [a025z100205.xml]
I will be very appreciated for your help.
Best regards
Ewelina