topic Script to load data from XML file in QlikView

Script to load data from XML file

Wed, 11 May 2011 18:21:08 GMT

Hello Everybody,

I have the following xml file :


<REPORT TITRE="AAA">
     <Param>
          <Box name="azerty" Id="17" />
     </Param>
     <HISTORY Name="xyz4" Var="Var1">
          <data date="04/05/11 01:00:00">76994</data>
           <data date="04/05/11 02:00:00">76995</data>
          <data date="04/05/11 03:00:00">76996</data>
              ...
 
     </HISTORY>
     <HISTORY Name="xyz4" Var="Var2">
          <data date="04/05/11 01:00:00">76997</data>
           <data date="04/05/11 02:00:00">76998</data>
          <data date="04/05/11 03:00:00">76999</data>
              ...
     </HISTORY>
               ...
</REPORT>

My question is to know, how to get a table with fields date time data and Id where Var = Var1?

I tried many things like this :

LOAD date,
     data,
     Id
FROM [file.xml] (XmlSimple, Table is [REPORT])
WHERE Var='Var1';

Thank you,

Jérémy

Script to load data from XML file

rbecher — Wed, 11 May 2011 19:05:34 GMT

Something like this:

data:

LOAD data%Table as data,

%Key_HISTORY_578652BE74995C98 // Key to parent table: REPORT/HISTORY

FROM C:\buffer\test.xml (XmlSimple, Table is [REPORT/HISTORY/data]);

JOIN LOAD date,

%Key_HISTORY_578652BE74995C98 // Key to parent table: REPORT/HISTORY

FROM C:\buffer\test.xml (XmlSimple, Table is [REPORT/HISTORY/data]);

INNER JOIN LOAD

%Key_REPORT_BE57120470E6A47F,

%Key_HISTORY_578652BE74995C98 // Key for this table: REPORT/HISTORY

FROM C:\buffer\test.xml (XmlSimple, Table is [REPORT/HISTORY])

Where Var='Var1';

INNER JOIN LOAD [Param/Box/Id] as Id,

%Key_REPORT_BE57120470E6A47F // Key for this table: REPORT

FROM C:\buffer\test.xml (XmlSimple, Table is [REPORT]);

// End of [test.xml] LOAD statements

Drop Field %Key_HISTORY_578652BE74995C98, %Key_REPORT_BE57120470E6A47F;

- Ralf

Script to load data from XML file

ToniKautto — Wed, 11 May 2011 19:26:28 GMT

You should load by using the [Table Files...] from the script editor, and them select file type XML. Here you will see what table your XML contains, which for the sample code means the table with Var is REPORT/HISTORY and not REPORT. In the Load as specified below you can add the WHERE clause for Var;

HISTORY:

LOAD Name,

Var,

%Key_REPORT_BE57120470E6A47F, // Key to parent table: REPORT

%Key_HISTORY_578652BE74995C98 // Key for this table: REPORT/HISTORY

FROM data.xml (XmlSimple, Table is [REPORT/HISTORY]);

Re: Script to load data from XML file

Mon, 30 Jul 2012 08:48:24 GMT

As a followup to this - I prefer to be able to not have to auto-generate the script - is there a way to get the long hex number without having to generate it from the script? Also will this number change depending on the content of the xml?