Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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;
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;
Hey Jonathan: thank you!!!
Other than for removing the $() on the table names and adding ":", it worked perfectly!
Thank you!