Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sydtriman
Contributor III
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);

1 Solution

Accepted Solutions
jonathandienst

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

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

sydtriman
Contributor III
Contributor III

Hey Jonathan:  thank you!!!

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

Thank you!