Identify source field value in FROM_FIELD load

[mk_kmx]

Hi all,

I want to use

LOAD ... From_Field (...)

to extract data from a field containing XML structured data. Let's say this is not an issue and I managed to successfully code that (at least on some PoC data).

I'm struggling to connect records in the result of this load with the record in the source table.

As an example - let's say we have a table with Invoices, which contains Invoice Items stored in a single field as XML. LOAD ... From_Field generates the table with all of the Invoice Items, but unless there is a reference to Invoice ID included in XML, I'm not able to link Invoice Items to the specific Invoice.

Also, if the structure of embedded XML is slightly more complex (and therefore I need to load the data using multiple LOAD statements), I have difficulties connecting these tables (autogenerated keys from XML loads reset to 0 with each record). I found out that FileName() function helps, but I'm not really sure if this is the right solution.

Any ideas?

Many thanks.

[rwunderlich]

If your end goal is to get one row per product in the invoice, and you are on Qlik Cloud, I think you can do it easier with the SubFieldRegEx() function. For example:

invoice_records:

NoConcatenate Load *

Inline [

invoiceID, product_names_csv

uuid-1, 'shampoo, burger,"bread sticks,(12)","sausage,(3)", eggs'

uuid-2, '"bread sticks,(24)", tuna'

uuid-3,',eggs,eggs,eggs, soap'

uuid-4,', , cheese, banana, orange, potato'

];

Products:

LOAD *,
  AutoNumber(RowNo(), invoiceID) as ProductSeq

Where Len(Trim(product)) > 0

;

LOAD 

  invoiceID,

  SubFieldRegEx(product_names_csv, ',(?=(?:[^"]*"[^"]*")*[^"]*$)|"') as product

Resident invoice_records; 

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

[mk_kmx]

Nice one, I this wouldn't cross my mind...

[rdugg]

Nice Regex  !  (but i'm on Qlik Sense, and really wanted to understand From_Field).

I have another question on that subject 

https://community.qlik.com/t5/Connectivity-Data-Prep/Why-does-this-Load-Form-Field-script-fail/m-p/2...

I find that Form_Field lacks documentation