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
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
AxNoxy
Contributor III
Contributor III

Join XML Tables without automatic generated Field

Hi there everyone,

I'm having an issue loading many XML's and joining them. The load process generates fields to be the keys of the many tables that my XML(many of them in fact)  must have. But, as I'm loading multiple XML the generated field may change the name for each new XML.

Scenario

MasterTable:

LOAD
Name          AS TableName,
%Key_DataSource_8E3A82EF69BDD2B6    AS MyTableKey
FROM  [lib://TestObtenArchivo (MYUSER)/Tipo 6.txt]
(xmlSimple, table is [Evaluacion/DataSource);

 

LEFT JOIN(MasterTable)

LOAD
Name          AS FieldName,
Value           AS FieldValue
%Key_DataSource_8E3A82EF69BDD2B6    AS MyTableKey, (This  field may change in the next load of a XML)
%Key_CharacteristicName_E36D3558627ED51C AS MyFieldKey (This  field may change in the next load of a XML)
FROM  [lib://TestObtenArchivo (MYUSER)/Tipo 6.txt]
(xmlSimple, table is [Evaluacion/DataSource/ListaCharacteristicName/CharacteristicName]);

 

Already known that those fields are generated automatically by each XML to make the correct join, but, in this case I need to LOAD a lot of XML where we have the next dis vantages.

  • The tables names are in the first load.
  • The field names and values are in the second load.
  • The Key between them its generated dynamically (In load I cant catch the new name of the keyfield_########).
  • We can't use @FieldPosition to get the position of the key and rename it.
  • We can't travel to upper levels, using the child node ([Evaluacion/DataSource/ListaCharacteristicName/CharacteristicName]) as a base table to call on the LOAD [Evaluacion/DataSource].
  • The fields names in the second load can be repeated (Any table loaded can have a field with the same name).

Pros:

  • Already have a xlsx file with the Tables and Fields correctly sorted.

At this point I cant find a solution for this, it will be very appreciated if I can find a guidance on this. @Gysbert_Wassenaar @Michael_Tarallo 

So much thanks in advance seriously.

 

Labels (3)
3 Replies
AxNoxy
Contributor III
Contributor III
Author

@rwunderlich  mentioned you because I saw some of your responses on other similar posts, hope you having a couple of minutes.

 

Thanks in advance!

Gysbert_Wassenaar

I think you'll have to use a loop to iterate through the individual files with a For Each loop using the FileList function to get the list of files. In each iteration you load the data from the xml file, join the two tables. Then drop the key field from the resulting table. Next concatenate all the records to a third table and drop the MasterTable.

talk is cheap, supply exceeds demand
AxNoxy
Contributor III
Contributor III
Author

Hi there Gysbert, first, thank you for you response, and about it In having the struggling joining the two tables in the loop, this because the key generated changes their name on every new XML key_6262727 , key_662828 and go on. Still ya en found the solution.

Hope you having a nice day.