Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sydtriman
New Contributor III

Unable to load multiple XMLs

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,

      ,

      [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);

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Unable to load multiple XMLs

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,

      ,

      [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;

2 Replies
MVP
MVP

Re: Unable to load multiple XMLs

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,

      ,

      [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;

sydtriman
New Contributor III

Re: Unable to load multiple XMLs

Hey Jonathan:  thank you!!!

Other than for removing the $() on the table names and adding ":", it worked perfectly!

Thank you!