Why does this Load Form_Field script fail ?

[rdugg]

I'm using a Load Form_Field function to retrieve lists of items already loaded in Qlik

Questions :

Why does the following script crash if I don't drop table invoice_records

Why does it stop crashing if I drop it ?

// why does this script crash ?
invoice_records:
NoConcatenate Load *
Inline [
invoiceID, product_names_csv
uuid-1, 'shampoo, burger,"bread sticks,(12)", eggs'
uuid-2, '"bread sticks,(24)", tuna'
uuid-3,',eggs,eggs,eggs, soap'
uuid-4,', , cheese, banana, orange, potato'
uuid-5,
uuid-6,'"pot ato"'
uuid-7,',eggs'
];

invoice_records2:
NoConcatenate Load
  RecNo() as rowid,
  invoiceID,
  EmptyIsNull(product_names_csv) as product_names_csv
  Resident invoice_records
  Where not IsNull(EmptyIsNull(product_names_csv));

// Drop table invoice_records;  // <-- why uncommenting this line prevents Form_field instruction from crashing

empty_result_table:
Load 1 as DummyField AutoGenerate 0;

Concatenate(empty_result_table)
Load *
  FROM_FIELD (invoice_records2, product_names_csv)
(txt, utf8, no labels, delimiter is ',', msq);

[marcus_sommer]

Crash means really crashing the application or a load-error is occurring? If it's an error what's the message?

I use from_field very rarely and therefore I'm not sure about the behaviour but I could imagine some kind of conflict if the field belonged to two tables and/or have a different number of field-values. Your noconcatenate-statements will prevent that data-tables are merged - if they had the identically field-structure which is here not the case. But the system-table of a field exists only ones and is shared between all data-tables which contain it. If this is really related to your issue you may avoid it by renaming the field within the second load to product_names_csv_X.

Beside this you may not need the second load else applying everything within the first one.

You didn't mention the aim behind the measure but I think that only resolving the string-lists into dedicated in n records may not sufficient because these data have no relations to their sources anymore. If this kind of information is needed the invoiceID might be added to the string and later extracted again.

Personally I would tend to another approach and trying to apply a loop with subfield(), like:

load *, subfield(product_names_csv, ',') as NewField
from Source;

whereby commas within the values are tricky to handle (but it's the same as with from_field). In such cases the string needs to be prepared in beforehand by replacing the comma with an appropriate place-holder.

[rdugg]

Yes, a load error is occurring :

The following error occurred:
Cannot open file: '***'
The error occurred here:
Concatenate(empty_result_table)

Load *

  From_Field (invoice_records2, product_names_csv)

(txt, utf8, no labels, delimiter is ',', msq)

The aim here is just to understand what makes the script crash, or not depending on drop table statement.

Is it a Qlik Engine bug ? Is there a good reason for that load error ? It's not a design question.

(By the way, I already got an answer about manually parsing the csv string)

[rdugg · Accepted solution]

---

@marcus_sommer wrote:

But the system-table of a field exists only ones and is shared between all data-tables which contain it. If this is really related to your issue you may avoid it by renaming the field within the second load to product_names_csv_X.

---

you are right, a rename of the field fixes it.

EmptyIsNull(product_names_csv) as product_names_csv2
....
Concatenate(empty_result_table)
Load *
  From_Field (invoice_records2, product_names_csv2)
(txt, utf8, no labels, delimiter is ',', msq);

I find it really weird that it does the "Load" on every values of the field globally and not just on the values inside the invoice_records2 table.

Plus that makes no sense with the fact that the table name is the first parameter of Form_Field.  ¯\_(ツ)_/¯

[marcus_sommer]

I don't think that there is a bug else it's a feature which functionality and limitations aren't clear enough documented.

That a field exists only ones within the data-model and it's shared between all tables is intentionally designed and part of the biggest strengths of the Qlik data-handling - reducing the CPU/RAM/storage consumption significantly and enabling the associative relation-ship between the data.