Using Alias in Subsequent Calculations

[#1]

I am relatively new to Qlikview and am trying to master the Scripting language.

I have an estimate that is built up in a transactional table over time. The estimate amounts are contained in a single field [TransAmt].

I would like to parse out the data into multiple fields, depending upon the value of the [Cost Column] field. I have managed to do this satisfactorily with the attached if/then/else constructs.

However, I would then like to perform calcultions on the parsed fields to create yet further fields. eg. (Actual_T + Accrual_T) as Incurred_T, also as shown below.

However, when I add that line, I get an error on reloading the script "Field not found - <Actual_T>".

I tried adding a new load statement / table etc., but kept getting the same error.

Is there anyway around this? It seems that expression can only rely on the results of explicit fields, and not on the results of other expressions.

load

[CompileID]

,

[MonthID]

,

if

([Cost Column]='budget', TransAmt,0) as Budget_T,

if

([Cost Column] = 'Commitment', TransAmt,0) as Commitment_T,

if

(([Cost Column]) = 'Actual',TransAmt,0) as Actual_T,

if

([Cost Column] = 'Accrual',TransAmt,0) as Accrual_T,

if

([Cost Column] = 'ETC',TransAmt,0) as [ETC_T],

(

Actual_T + Accrual_T) as Incurred_T

Resident

EstimateItems_T;

[Or]

As far as I know, there's no way to use load-based aliases in the load block that generated them. However, you can use them in subsequent loads, e.g.

myLoad:
Load myColumn, left(myColumn,2) as myAlias
resident myTable;

Load *, left(myAlias,1) as mySecondAlias
Resident myLoad;

drop table myLoad;

There's similar ways to achieve the same thing without loading a temporary table and dropping it (by using LEFT JOIN on a unique key, for example).

Hope this helps

[#3]

Thanks Or,

That worked well - saved me a lot of experrimentation.

Greg

[#4]

Hi Or Shoham,

how can I use LEFT JOIN to achieve the same result?

I'm trying without success....

Thank you a lot

Or Shoham wrote:
As far as I know, there's no way to use load-based aliases in the load block that generated them. However, you can use them in subsequent loads, e.g.
 <blockquote>

myLoad:
Load myColumn, left(myColumn,2) as myAlias
resident myTable;

Load *, left(myAlias,1) as mySecondAlias
Resident myLoad;

drop table myLoad;<pre>

There's similar ways to achieve the same thing without loading a temporary table and dropping it (by using LEFT JOIN on a unique key, for example).

Hope this helps

[#5]

An easoest way in order to avoid temporary table:

myFinalTable:

Load *, left(myAlias,1) as mySecondAlias;

Load myColumn, left(myColumn,2) as myAlias

resident myTable;

This way it will generate only one table with the data you need !

More complicated to read but easier to generate...

Rgds,

Sébastien

[#6]

Thanks spastor, very good solution.

I have another problem like this with the join. I write below an example:

myFinalTable:

Load *, left(myAlias,1) as mySecondAlias, left(yourAlias,1) as yourSecondAlias;

Load myColumn as ID, left(myColumn,2) as myAlias

resident myTable;

left join

Load yourColumn as ID, left(yourColumn,2) as yourAlias

resident yourTable;

Error: "yourAlias" not found.

Thanks again!

spastor wrote:
An easoest way in order to avoid temporary table:

myFinalTable:
Load *, left(myAlias,1) as mySecondAlias;
Load myColumn, left(myColumn,2) as myAlias
resident myTable;
This way it will generate only one table with the data you need !
More complicated to read but easier to generate...
Rgds,
Sébastien
 <div></div>