topic Re: What is FROM_FIELD for? in QlikView

What is FROM_FIELD for?

gussfish — Wed, 30 Oct 2013 04:53:05 GMT

Hi folks,

I've just re-discovered the LOAD ... FROM_FIELD syntax. I even understand it! What I don't understand is why the statement exists. It seems to me that this statement:

LOAD

FROM_FIELD (MyTable,MyField) (format_spec);

is exactly equivalent to

LOAD

MyField

Resident MyTable;

Can anyone tell me if FROM_FIELD has any advantages over doing a regular LOAD ... Resident?

Angus.

Re: What is FROM_FIELD for?

tresB — Wed, 30 Oct 2013 05:02:06 GMT

Perhaps, when you tell to face a single field from a 100-fields table would not be as efficient as from a single field itself, becasue the scope of search is optimized in this case.

Re: What is FROM_FIELD for?

Wed, 30 Oct 2013 05:38:45 GMT

Hi Treseco,

Do you have any example where you used it?

- Yojas

Re: What is FROM_FIELD for?

Gysbert_Wassenaar — Wed, 30 Oct 2013 07:09:35 GMT

The FROM_FIELD allows you to process an earlier loaded field as if it is a table. See attached example. I don't see much advantage over using the subfield function directly.

Re: What is FROM_FIELD for?

Wed, 30 Oct 2013 10:17:36 GMT

Hi G Wassenaar,

In your example I am not understanding why I am getting 333|c and why I am not getting 1414|n?

Can you please explain me that? and how you write (txt, utf8, explicit labels, delimiter is '|', msq) this syntax?

Thanks!!

- Yojas

Re: Re: What is FROM_FIELD for?

gussfish — Mon, 04 Nov 2013 03:01:37 GMT

Ah, I see your point, G Wassenaar: more precisely, FROM_FIELD allows you to process a previously-loaded field as if each value in it were itself the content of an external data file, parsing each such value in accordance with the format-spec. I observe, too, that the results of parsing all those 'files' are then concatenated.

It strikes me that this is particularly useful when each value in an external file is itself structured, complex data.

e.g. a database might have a column in which each value is HTML (containing a <table/> at least, but even an entire <html> document). Here, an SQL SELECT statement will load the values into a table, and LOAD ... FROM_FIELD can then be used to parse those HTML fragments and load a particular table in each of those fragments.

See attached example.

A weakness of FROM_FIELD, though, is that if the data containing the source field of the LOAD...FROM_FIELD statement contains another field (e.g. a key field) and that fields values need to be associated with the resulting data, there's no simple way to achieve this.

Angus.

Re: What is FROM_FIELD for?

Mon, 11 Nov 2013 09:38:08 GMT

Hi Angus,

Thanks for your explanation!!

But can you tell me how you write :

* inline [HTMLDoc<html><body><table><tr><td>Col1</td><td>Col2</td></tr><tr><td>123</td><td>321</td></tr></table></body></html>
<table><tr><td>Col1</td><td>Col2</td></tr><tr><td>456</td><td>654</td></tr><tr><td>789</td><td>987</td></tr></table>];

And how you write :

html, codepage is 1252, embedded labels, table is @1);

Please help me out with this.. Is this the code you got from internet or you write this??

- Yojas

Re: What is FROM_FIELD for?

gussfish — Wed, 13 Nov 2013 05:34:23 GMT

Hi Yojas,

I hand-typed the actual HTML myself. If you want to know how to write HTML, there are heaps of tutorials on the Web e.g. http://www.w3schools.com/html/
I also hand-typed the HTML's surrounding inline clause i.e. "* inline [HTMLDoc...];" The syntax of the LOAD statement (i.e. the rules for how to write a LOAD statement) is documented in the QlikView Reference Manual.
I used QlikView Developer's Table Files wizard to build the format-spec i.e. "html, codepage is 1252, embedded labels, table is @1". To do this, I used Notepad to type the HTML into a text file that I then renamed as 'sample.html'; then, in the QlikView Developer script editor, I pressed the "Table File..." button, selected sample.html, and used the wizard in the usual way to create a LOAD statement for that file. I then copied the format-spec from that LOAD statement.

Angus.

Re: What is FROM_FIELD for?

Thu, 14 Nov 2013 06:22:01 GMT

Hi Angus,

Thanks for the reply...