2 Replies Latest reply: Oct 5, 2015 5:23 PM by Michael Cox RSS

    Unable to load multiple XMLs

    Michael Cox

      New to Qlik Sense and would appreciate any and all help.

       

      I am trying to load multiple xml files (one for each year), the structure of which is shown below. I'm able to get my LOAD script to load one file, but not multiple. I either get lots of synthetic tables or the data just doesn't load right.  Any suggestions?

       

      XML format

       

      <Request xmlns="http://abc.com/lodge.xsd">

        <CalculationId>0d47648</CalculationId>                /// UNIQUE FOR EACH FILE

        <EntityName>ABC Ltd</EntityName>

         <Scenario>ABC 31-12-2014</Scenario>

        <Year>2014</Year>

      <Data>

          <ImportFile>

            <Form FormCode="WWW_006_02_01" FormName="2014 Financials" >

              <El C="RP" ID="AddressDetails.AddressDetails.Currency.Code">C</El>

              <El C="RP" ID="AddressDetails.AddressDetails.Line1.Text">Suite 100B</El>

              <El C="RP" ID="AddressDetails.AddressDetails.Line2.Text">GPO BOX 418</El>

           ...... (etc)

           </Form>

           <Form .....>

           .... (etc)

           </Form>

      </Data>

      </Request>

       

      My LOAD script (which works for 1 file) looks like this

       

      lodgment:

        LOAD [xmlns],

            [CalculationId],

            [EntityName],

            [Scenario],

            [Year]

      FROM [lib://import/2015.xml]

        (XmlSimple, table is Request);

       

      data:

        LOAD [El%Table] as efile_values,

            [C],

            [ID]

      FROM [lib://import/2015.xml]

        (XmlSimple, table is [Request/Data/ImportFile/Form/El]);

       

        left join data

       

      LOAD [CalculationId]

      FROM [lib://import/2015.xml]

        (XmlSimple, table is Request);

        • Re: Unable to load multiple XMLs
          Jonathan Dienst

          I assume that you are looping to load all the files. If so, you cannot do join inside the loop. A join adds fields, and after the first iteration those fields exist, so they get included in the join key for the next iteration. You need to accumulate the two tables inside the loop and then join after the loop. You many also need to explicitly concatenate the tables.

           

          Something like this:

           

          Set vConcat1 = 'lodgement:';

          Set vConcat2 = 'data:';

          Set vConcat3 = 't_join:';

           

          ForEach vFile in FileList('....')   

              $(lodgement)

              LOAD [xmlns],

                [CalculationId],

                [EntityName],

                [Scenario],

                [Year]

              FROM [lib://import/2015.xml]

              (XmlSimple, table is Request);

              

              $(data)

              LOAD [El%Table] as efile_values,

                [C],

                [ID]

              FROM [lib://import/2015.xml]

              (XmlSimple, table is [Request/Data/ImportFile/Form/El]);

              

              $(t_join)

              LOAD [CalculationId]

              FROM [lib://import/2015.xml]

                (XmlSimple, table is Request);

               

              Set vConcat1 = 'concatenate(lodgement)';

              Set vConcat2 = 'concatenate(data)';

              Set vConcat3 = 'concatenate(t_join)';   

          Next

           

          left join (data)

          LOAD * Resident t_join;

           

          DROP Table t_join;