3 Replies Latest reply: Mar 17, 2016 12:25 PM by Chanty 4u RSS

    Connecting two different tables from XML file

    Øyvind Monsen

      I am trying to connect info from one table to another from a XML-file.

       

      I have several XML-files with similar structure, which I want import to clickview:

       

      XML-file example:

       

      <Register>

           <Details>

                <Car> Toyota </Car>

           </Details>

           <Fuel>

                <result Station="Shell" SampleDate="211215" ControlNoCd="1122332 ">

                    <Amount> 213L </Amount>

                    <Quality> A4 </Quality>

                </result>

                <result Station="Statoil" SampleDate="191115" ControlNoCd="1156332 ">

                    <Amount> 140L </Amount>

                    <Quality> A1 </Quality>

                </result>

                <result Station="Chevron" SampleDate="011215" ControlNoCd="367332 ">

                    <Amount> 300L </Amount>

                    <Quality> A1 </Quality>

                </result>

           </Fuel>

      </Register>

       

      There is only one <Car> for each file - so this is unique for each file - all the info under result is connected to this specific car. Next file can be the same car, or another one. Problem is that I want to connect Details table with all the info under the Fuel table.

       

      I have tried to write it like this:

       

      LOAD

           [Details/Car] as Car,

           [Fuel/Result/Station] as Station,

           [Fuel/Result/SampleDate] as SampleDate,

           [Fuel/Result/ControlNoCd] as ControlNoCd,

           [Fuel/Result/Amount] as Amount,

           [Fuel/Result/Quality] as Quality,

          %Key_report_16926E6E9269865A    // Key for this table: register

      FROM [H:\Files\*.xml] (XmlSimple, Table is [Register]);

      // End of [*.xml] LOAD statements

       

      The problem I meet with this script, is that only the first Result-node is displayed. After it is finished with results from station "shell", it does not take out the values from "Statoil" and "Chevron"...

       

      It has to be said, I am no programmer, but trying to make a simple script to implement it in qlikview with my limited knowledge...