topic Re: Reference field from a previously loaded table in script in QlikView

Reference field from a previously loaded table in script

datanibbler — Tue, 23 Apr 2013 14:35:24 GMT

Hi,

I think a QlikView script is executed top-down and from left to right. Is that correct? I am trying to introduce a new field in a table within the LOAD statement that should be referencing a field from a previously loaded table (the LOAD statement for that table is on a tab to the left of the one I'm editing).

Can anybody lend me a hand there? I can't seem to get it right: I keep getting the script error message >Field not found<

Currently, this is what I've got (let's say the table on the left-hand tab is called TABLE_A and the one on the right-hand tab is called TABLE_B (those are the original names):

IF(TABLE_A.TYPE = 'R', TABLE_B.Quantity, 0) as Quantity_net

Why can't QlikView find the field TABLE_A.TYPE at this point?

Thanks a lot!

Best regards,

DataNibbler

Re: Reference field from a previously loaded table in script

Tue, 23 Apr 2013 15:05:15 GMT

Friedrich, Qlikview actually resolves bottom - up, by which I mean that the load statement should be below the one that requires it. Weird, I know.

Re: Reference field from a previously loaded table in script

datanibbler — Tue, 23 Apr 2013 15:15:12 GMT

Hi other_peoples_shoes,

no, unfortunately that doesn't work in my case.

When you say "bottom up" you also mean "right to left" (between tabs), don't you? Weird indeed - but that doesn't work - when I swap the tabs so that the LOAD statement where the field_to_be_referenced is loaded is on the right and the LOAD statement with the field_that_refers_to_it is on the left, the result is the same.

Best regards,

DataNibbler

Re: Reference field from a previously loaded table in script

Tue, 23 Apr 2013 15:29:36 GMT

Friedrich,

I actually meant bottom up and left to right, as far as the tabs go.

Does your code include a Load Resident statement? The two tables might not be talking to each other. Also, the table.variable syntax is bad form in QV. Try just using variable names.

Re: Reference field from a previously loaded table in script

datanibbler — Wed, 24 Apr 2013 07:23:45 GMT

Hi,

no, there is no RESIDENT load, the two tables are independently loaded from two qvd files. I will try by rearranging the tabs for "left-to-right and bottom-up".

Thanks a lot!

Re: Reference field from a previously loaded table in script

kuba_michalik — Wed, 24 Apr 2013 08:03:00 GMT

In QlikView loadscript you can't reference a previously loaded field in another LOAD statement just like that - associations start to work when you are done with reloading, not during it.

There are few rather specific ways you can reference fields from already loaded tables, but generally they boil down to two things:

- resident load - you load an already loaded table again. There is also preceding load, which is the only "bottom up" situation in the script - it is like an optimised resident load, where instead of specifying the resident table to read from it is taken directly from the next LOAD statement.

- inter-record functions (peek, fieldvalue, lookup, etc), which find a value in a previously loaded field.

Also, field names are globally scoped. If you have a field TYPE in TABLE_A, then you refer to it as TYPE. If you try to refer to TABLE_A.TYPE it will throw an error because there is no field named TABLE_A.TYPE (there is no table qualifier part - it is all a variable name, even if you choose to name all fields in TABLE_A to start with TABLE_A. for clarity).

Re: Reference field from a previously loaded table in script

datanibbler — Wed, 24 Apr 2013 09:57:19 GMT

Hi Kuba,

I thought so, but it's quite a different thing to guess something or to know it for sure. Thanks for the clarification!

I guess the PEEK function that I just read about is the only exception to this rule.

Well, I can do without this, I can edit the formula in my diagram instead. That doesn't work for some reason, but I will make it eventually.

Thanks a lot!

Best regards,

DataNibbler