Can I create a calculated field using another calculated field?

[caseyjohnson]

New to Qlik,

I created a simple calculated field in the data manager and want to use that calculation to be used in other calculated fields. The data manager does not recognize the newly created calculated field and I'm unable to use it for further calculated fields.

First successful calculated field:       Sum(fieldA + fieldB + fieldC) = total_cost_field

2nd desired calculated field:             (fieldX) / total_cost_field

So far I've found this info: "You can add calculated fields to manage many cases like this. A calculated field uses an expression to define the result of the field. You can use functions, fields and operators in the expression. You can only refer to fields in the table that you are editing."

Questions:

1. Can I perform these tasks in the Load Editor?
2. If you can't refer to calculated fields in your calculations, do you need to just recreate the primary calculation in your next calculated field? (ex: (fieldX / [Sum(fieldA + fieldB + fieldC)] )

[sunny_talwar]

Would you be able to provide a sample of what you have and trying to get?

[Dustin_Baxa]

Hi Casey,

Unfortunately you cannot refer to calculated fields when creating a new calculated field.

To answer your questions:

1. Can I perform these tasks in the Load Editor?

Yes you can! If you created your first calculated field (total_cost_field) in the load script, then you could refer to that field in your expression editor when creating a calculation within the dashboard. Or, you could create both in the load editor. But you would have to recreate the primary calculation in your next calculated field.

Here is an example of the load script you would need to create the calculated field. Notice that you need to use a resident load. A resident load is when you load in your data, then do an additional load from that data that is now in memory.

[Temp]:

LOAD

  [FieldA],

  [FieldB],

  [FieldC]

FROM [lib://Desktop/Delete.xlsx]

(ooxml, embedded labels, table is Sheet1);

New:

LOAD

  [FieldA],

  [FieldB],

  [FieldC],

sum(FieldA + FieldB + FieldC) as Total_Cost_Field

    resident Temp

group by FieldA,FieldB,FieldC;

    drop table Temp;

2. If you can't refer to calculated fields in your calculations, do you need to just recreate the primary calculation in your next calculated field? (ex: (fieldX / [Sum(fieldA + fieldB + fieldC)] )

Exactly.

Dustin

[rwunderlich]

You can't refer to the calculated field by name at the same level of the LOAD statement, but you can use a preceding load to refer to the field like this. So there is no need to duplicate the primary calculation. Note that you are still restricted to using only fields present in or derived from the single input table.

New:

LOAD

    *,

    Total_Cost_Field * .1 as Tarrif

;

LOAD

  [FieldA],

  [FieldB],

  [FieldC],

sum(FieldA + FieldB + FieldC) as Total_Cost_Field

    resident Temp

group by FieldA,FieldB,FieldC;

See

Preceding Load

Preceding Load is Elegant | Qlikview Cookbook

-Rob

http://masterssummit.com

http://qlikviewcookbook.com