Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

2 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Hey Jonathan:  thank you!!!

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

Thank you!