topic Re: XML Parent - Child Tables in loading multiple files in QlikView

XML Parent - Child Tables in loading multiple files

ChristopherLopMX — Sat, 16 Nov 2024 18:32:28 GMT

Hello,

I've been looking several posts about this, and I think there are no solutions discuss in the forums, if you happen to know a solution post, please share it with me. 🙂

This is the issue:

I am loading multiple XML files, an app, the problem is that most of this files have parent-child tables, you can see this in the following visualization of the XML file:

I the image you can see for example, how the node: "<cfdi:Conceptos>" its in fact a table with different invoice concepts, but inside this node, there is an other table, from the node: "<cfdi:Traslados>", but this one is a child of :<cfdi:Conceptos>", those two are child tables of: "<cfdi:Comprobante>". When I run the file load wizard, qlikview creates a key to tie the dimensions inside each tables together, for example, if I need the product code, and the tax id from different tables, if this two where related like parent-child in the XML files, then the key will link them together , it does it from the wizard and the key is named: "%Key_(Parent Table Name)_(Some ID generated by qlikview)", here is an example of two keys generated:

%Key_Comprobante_77D75F18365C7329, // Key to parent table: Comprobante
%Key_Concepto_3FF84D80079AAB80 // Key for this table: Comprobante/Conceptos/Concepto

The comments where also added by the wizard. This works perfect for single file loads, but when I load more than one file, I don't know how to generate a key to like parent and child elements together. I have tried by transforming the information inside each table and mixing in with files names or rates, but it doesn't work all the time, so I was thinking the only way is to find out how to create in script a similar dimension as the "autogenerated" keys the load file wizard sets in the file with individual file load.

I am attaching two XML for testing, also I am sorry but I dont have money to buy the business version so I am working with the personal edition, so if you could help me copy pasting your code in the answer would help me a lot. Thank you!

Re: XML Parent - Child Tables in loading multiple files

rwunderlich — Wed, 03 Jun 2020 23:20:27 GMT

The automatically generated key values get reset for each file. So the trick is to add something into the key per file:

filename() & %Key_Comprobante_77D75F18365C7329, as Key_Comprobante

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: XML Parent - Child Tables in loading multiple files

ChristopherLopMX — Wed, 03 Jun 2020 23:30:50 GMT

Just by adding the file name the %key will reset every new load? I will test it right away.

Re: XML Parent - Child Tables in loading multiple files

ChristopherLopMX — Wed, 03 Jun 2020 23:35:19 GMT

I worked like a charm man! I will do some tests to see if I have not lost some information and see if a can do some joins to create a single table, but so far it looks good. Thank you very much!!!

Re: XML Parent - Child Tables in loading multiple files

rwunderlich — Thu, 04 Jun 2020 04:45:57 GMT

As each xml file begins loading, %Key_Comprobante_77D75F18365C7329 is initialized to zero. It will get incremented by 1 for each nested table, thereby giving each nested table a unique key value.

When loading multiple files, you will end up with the key value of "0" being used multiple times. Prepending the filename() means you will have keys like "myFile1.xml0", "myOtherFile.xml0", resulting in unique values even when the key counter is reused.

-Rob