Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
surajap123
Creator III
Creator III

calcuated field without resident

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

10 Replies
swuehl
MVP
MVP

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
MVP
MVP

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
MVP
MVP

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

surajap123
Creator III
Creator III
Author

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

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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