calcuated field without resident

[surajap123]

Hi All,

I want to create a calculated field using 2 fields which are in different tables. I have left joined table2 to table1 and then created a resident table to create the needed calculated field.

Is there any other way to create it. I mean can i create the calculated field in table1 directly?

[swuehl · Accepted solution]

You can probably use a MAPPING approach or Lookup() function instead.

Post some more details about your input data (some sample lines of input data) if you require more help.

[its_anandrjs]

As Swuehl suggest you can use Mapping load, Lookup() functions but if there is relation between two table then you can do this on the front end also but the performance is slow in this process so i believe us the calculation in the load script.

[surajap123]

Hi Swuehl / Anand,

As suggested, i have created the lookup table and created applymap function in the table. But when i reload, i am getting the error ' field Hours are not present in Fact'

Hrs:

Mapping

LOAD Id,

Hours;

SQL SELECT * FROM ...;

Fact:

Id,

ApplyMap('Hrs',Id) as Hours,

(Units * Hours) as TotalHours,

Units

SQL SELECT * FROM...;

please suggest

[surajap123]

I found that the error is because of the calculated field-

(Units * Hours) as TotalHours

The Hours field is coming from mapping table, as a result I get error 'field Hours is not present in Fact'.

My actual requirement is to create the calculated field. how to resolve this issue..??

[swuehl]

Use a preceding load in your Fact Table load:

Fact:

LOAD *, (Units * Hours) as TotalHours;

Id,

ApplyMap('Hrs',Id) as Hours,

Units

SQL SELECT * FROM...;

[stevedark]

HI Swuehl,

Should that statement not have another LOAD in it? 

Fact:

LOAD

*,

(Units * Hours) as TotalHours

;

LOAD

Id,

ApplyMap('Hrs',Id) as Hours,

Units

;

SQL SELECT * FROM...;

And an extra semi colon...

Steve

[swuehl]

You are right Steve, sorry for a really bad copy & paste.

[surajap123]

The preceding load is working for me.. Thanks everyone..

[stevedark]

Regarding ApplyMap and nesting them with precedings you may want to see this blog post:

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Steve