Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi guys,
how can I load the following xml into qv and get report date, report status, report_code, code, currency_desc, qualifier, volume as columns?
<report date="8-14-2015">
<report status="Live">
<identification report_code="123" report_name="Sales Per Region" report_date="8-14-2015" report_time="200" report_version="100"/>
<data>
<row_ AAA code="11">
<currency_desc>USD</currency_desc>
<qualifier>BA</qualifier>
<volume>50</volume>
</row_AAA>
<row_AAA code="MM">
<currency_desc>EUR</currency_desc>
<qualifier>BB</qualifier>
<volume>200</volume>
</row_AAA>
Thanks,
Alec
This is an improper XML file as there is no closing tag for the first two tags or for the data tag, and there is an invalid "row_ AAA" tag - or is that just how you cut and pasted?
Assuming that a valid form of the file is:
<report date="8-14-2015">
<report status="Live">
<identification report_code="123" report_name="Sales Per Region" report_date="8-14-2015" report_time="200" report_version="100"/>
<data>
<row_AAA code="11">
<currency_desc>USD</currency_desc>
<qualifier>BA</qualifier>
<volume>50</volume>
</row_AAA>
<row_AAA code="MM">
<currency_desc>EUR</currency_desc>
<qualifier>BB</qualifier>
<volume>200</volume>
</row_AAA>
</data>
</report>
</report>
The load script would be:
report:
LOAD
[report/identification/report_date] as report_date,
[report/status] as report_status,
[report/identification/report_code] as report_code,
%Key_report_16926E6E9269865A
FROM
Join (report)
LOAD code,
currency_desc,
qualifier,
volume,
%Key_report_16926E6E9269865A
FROM
DROP Field %Key_report_16926E6E9269865A; // not needed any more
This is an improper XML file as there is no closing tag for the first two tags or for the data tag, and there is an invalid "row_ AAA" tag - or is that just how you cut and pasted?
Assuming that a valid form of the file is:
<report date="8-14-2015">
<report status="Live">
<identification report_code="123" report_name="Sales Per Region" report_date="8-14-2015" report_time="200" report_version="100"/>
<data>
<row_AAA code="11">
<currency_desc>USD</currency_desc>
<qualifier>BA</qualifier>
<volume>50</volume>
</row_AAA>
<row_AAA code="MM">
<currency_desc>EUR</currency_desc>
<qualifier>BB</qualifier>
<volume>200</volume>
</row_AAA>
</data>
</report>
</report>
The load script would be:
report:
LOAD
[report/identification/report_date] as report_date,
[report/status] as report_status,
[report/identification/report_code] as report_code,
%Key_report_16926E6E9269865A
FROM
Join (report)
LOAD code,
currency_desc,
qualifier,
volume,
%Key_report_16926E6E9269865A
FROM
DROP Field %Key_report_16926E6E9269865A; // not needed any more
thank you Jonathan!