topic Re: Multiple Calculated field in LOAD in QlikView

Multiple Calculated field in LOAD

chris1987 — Tue, 26 Jan 2016 12:08:23 GMT

Hi,

I need to import the various fields from a .csv but I also need to add multiple calculated fields, some of which are based on the previous calculated field. The problem is, because the field is being calculated in the same statement I cannot use the new field name.

Here’s a simple version of my code, which produces the error: “Field not found - <Order-Value>”

LOAD

[Item Ordered],

[Qty Ordered],

[Qty Shipped],

[Value per Item],

([Qty Ordered] * [Value per Item]) as [Order-Value],

([Qty Shipped] * [Value per Item]) as [Ship-Value],

([Order-Value] - [Ship-Value]) as [Outstanding-Value]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

This is only an example of my report, I’m not looking for a way to get the “Outstanding-Value” – I know in the above example I could easily do this by ([Qty Ordered] -[Qty Shipped] * [Value per Item]) as [Outstanding-Value]. I’m looking for a way to refer to calculated fields in a Load statement.

The reason I’m looking at this I currently have an excel/VBA report that I’m using but want to convert it to QlikView, there are currently 20 calculated fields in the excel version , a lot of which refer to each other.

Thanks in advance for your help!

Chris

Re: Multiple Calculated field in LOAD

sunny_talwar — Tue, 26 Jan 2016 18:00:04 GMT

Try using Preceding load here: (Qlik Design Blog : Preceding Load | Qlik Community)

LOAD *,

([Order-Value] - [Ship-Value]) as [Outstanding-Value];

LOAD

[Item Ordered],

[Qty Ordered],

[Qty Shipped],

[Value per Item],

([Qty Ordered] * [Value per Item]) as [Order-Value],

([Qty Shipped] * [Value per Item]) as [Ship-Value]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Multiple Calculated field in LOAD

chris1987 — Wed, 27 Jan 2016 10:01:05 GMT

Thanks, that did work, but I also want to know how I can then use the new "Outstanding-Value" in an expression. Say for example I want to add 20% tax to the "Outstanding-Value" figure?

Here's what I've tried with no success (Field not found error):

LOAD *,
([Order-Value] - [Ship-Value]) as [Outstanding-Value];

LOAD *,
([Outstanding-Value] * 1.2) as [Tax];

LOAD
[Item Ordered],
[Qty Ordered],
[Qty Shipped],
[Value per Item],
([Qty Ordered] * [Value per Item]) as [Order-Value],
([Qty Shipped] * [Value per Item]) as [Ship-Value]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

I hope you can see what I'm trying to do. I have a lot of fields which the value is determined on the values in other calculated fields (that will be created when the reload is ran).

Cheers

Chris

Re: Multiple Calculated field in LOAD

sunny_talwar — Wed, 27 Jan 2016 10:43:58 GMT

The order is not correct. You need to go bottom up. In preceding load, the code is read bottom uo:

LOAD *,
([Outstanding-Value] * 1.2) as [Tax];

LOAD *,
([Order-Value] - [Ship-Value]) as [Outstanding-Value];

LOAD
[Item Ordered],
[Qty Ordered],
[Qty Shipped],
[Value per Item],
([Qty Ordered] * [Value per Item]) as [Order-Value],
([Qty Shipped] * [Value per Item]) as [Ship-Value]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Multiple Calculated field in LOAD

chris1987 — Wed, 27 Jan 2016 10:46:29 GMT

Perfect, Thanks!