topic Re: Using calculated fields in QlikView

Using calculated fields

Tue, 15 May 2012 10:07:43 GMT

Hi everybody,

as a newcomer to QLIKVIEW, I have a special problem in using the Load-Command. With the following statement:

LOAD X,

X*Y*Z as PRO,

X+Y+Z as SU

FROM

m3.xlsx

(ooxml, embedded labels, table is Tabelle1);

I try to read some random numbers (X, Y, Z) from an Excel chart; the field PRO und SU are calculated, and this works fine.

But when I try the following:

LOAD X,

X*Y*Z as PRO,

X+Y+Z as SU,

TEST as PRO*SU

FROM

m3.xlsx

(ooxml, embedded labels, table is Tabelle1);

an error occurs (field TEST not found). My question ist: is it generally not possible to refer to calculated fields while loading data from a file in this way?

Thanks and regards! WS

Using calculated fields

swuehl — Tue, 15 May 2012 10:12:42 GMT

Do you mean

...

PRO*SU as TEST

...

(Calculating the new field TEST from PRO and SU?)

This is also not possible, since the fields PRO and SU are not known in the input table. You need to use a preceding load

LOAD *, PRO*SU as TEST;

LOAD X,Y,Z,X*Y*Z as PRO, Y+X+Z as SU from ...

or use the full expression:

LOAD

X,Y,Z,X*Y*Z as PRO, Y+X+Z as SU, X*Y*Z *( X+Y+Z) as TEST from...

Re: Using calculated fields

Jason_Michaelides — Tue, 15 May 2012 10:14:28 GMT

PRO and SU don't exist as fields yet so you can't use them to calculate TEST. 2 options:

LOAD X,

X*Y*Z as PRO,

X+Y+Z as SU,

(X*Y*Z) * (X+Y+Z) AS TEST

FROM

m3.xlsx

(ooxml, embedded labels, table is Tabelle1);

Data_temp:

LOAD X,

X*Y*Z as PRO,

X+Y+Z as SU

FROM

m3.xlsx

(ooxml, embedded labels, table is Tabelle1);

Data:

LOAD

PRO * SU AS TEST

RESIDENT Data_temp;

DROP TABLE Data_temp;

Hope this helps,

Jason

Re: Using calculated fields

Tue, 15 May 2012 10:20:23 GMT

Thanks a lot, this works fine! Kind regards! WS

Re: Using calculated fields

Tue, 15 May 2012 10:20:23 GMT

Thanks a lot, this works fine! Kind regards! WS

Using calculated fields

Anonymous — Tue, 15 May 2012 10:22:56 GMT

One more version, with shorter script:

Data:
LOAD
X,
Y,
Z,
X*Y*Z as PRO,
X+Y+Z as SU
FROM m3.xlsx (ooxml, embedded labels, table is Tabelle1);

LEFT JOIN (Data) LOAD
X,Y,Z,
PRO * SU AS TEST
RESIDENT Data;

But best of all, in most cases - just load X, Y, and Z, and calculate expressions on the front end.

Regards,
Michael

Re: Using calculated fields

Jason_Michaelides — Tue, 15 May 2012 10:47:52 GMT

Sorry Michael - I don't agree!

I was under the impression that LEFT JOINS were relatively inefficient in QlikView as it loads the whole second table before deciding what to join and keep. I might be wrong...

Also, I hardly ever recommend calculating in the front end instead of the script if the calculation isn't dependent upon current selections. You end up with unneccesarily complicated expressions and place uneeded overhead on the server. The only time I can see this being an advantage is if the extra script work would increase script run time and you had an overly tight reload window.

Finally - Stefan's preceding load script seems to be the shortest of all!

Jason

Using calculated fields

Anonymous — Wed, 16 May 2012 23:48:32 GMT

Jason,

I'm not going to argue with you at this thread - it is closed anyway. Just two points:
1. You missed my "qualifier": in most cases. Your consideration are applicable in cases where front end perfomance is at risk, that doesn't happen "in most cases", at least not in my world. Besides, in this specific situation these expressions do depend on selections, and don't add any value but add extra data, hence application size.
2. Author mentioned that he was a newcomer. I think it is important for a new person to start from the basics, learning what Qlikview is about and what is the right way of using it.

Regards,
Michael

Re: Using calculated fields

Thu, 17 May 2012 07:26:18 GMT

Hi,

I hope that my question did not initiate any trouble. I was very happy having help within - literally!!! - a few minutes. I have worked out the first proposal (using preceding loads), and it is really bombastic. Loading 1,95 Mio. lines, each with 9 decimal values, from a model file calculated in Mathematica, and calculating additional 10 fields in Qlikview takes about 14 seconds on an actual Notebook PC under Windows 7.

If you should wonder what this is useful for: I developed an OR model, representing various scenarios that can occur in the economic situation of an industrial plant I am working at. There are 9 independent dimensions to regard, each of which has 5 variations, this is why I have to deal with 1,95 Mio. lines.

Best regards!