topic Load xml file question in QlikView

Load xml file question

alec1982 — Fri, 14 Aug 2015 06:09:31 GMT

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?

<data>

<row_ AAA code="11">

<currency_desc>USD</currency_desc>

</row_AAA>

<row_AAA code="MM">

<currency_desc>EUR</currency_desc>

</row_AAA>

Thanks,

Alec

Re: Load xml file question

jonathandienst — Fri, 14 Aug 2015 06:21:44 GMT

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>

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 (XmlSimple, Table is [report]);

Join (report)

LOAD code,

currency_desc,

qualifier,

volume,

%Key_report_16926E6E9269865A

FROM (XmlSimple, Table is [report/report/data/row_AAA]);

DROP Field %Key_report_16926E6E9269865A; // not needed any more

Re: Load xml file question

alec1982 — Fri, 14 Aug 2015 06:42:51 GMT

thank you Jonathan!