Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
chris1987
Creator
Creator

Multiple Calculated field in LOAD

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

1 Solution

Accepted Solutions
sunny_talwar

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);

View solution in original post

4 Replies
sunny_talwar

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
);

chris1987
Creator
Creator
Author

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

sunny_talwar

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);

chris1987
Creator
Creator
Author

Perfect, Thanks!