Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
(
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
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
(
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
(
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
(
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
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
(
Perfect, Thanks!