topic Using Alias in Subsequent Calculations in QlikView

Using Alias in Subsequent Calculations

Mon, 28 Jun 2010 08:54:20 GMT

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]

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

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

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

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

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

(

Actual_T + Accrual_T) as Incurred_T

Resident

EstimateItems_T;

Using Alias in Subsequent Calculations

Or — Mon, 28 Jun 2010 09:23:38 GMT

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

Using Alias in Subsequent Calculations

Tue, 29 Jun 2010 02:39:49 GMT

Thanks Or,

That worked well - saved me a lot of experrimentation.

Greg

Using Alias in Subsequent Calculations

Tue, 28 Dec 2010 16:18:02 GMT

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

Using Alias in Subsequent Calculations

Tue, 28 Dec 2010 16:27:06 GMT

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

Using Alias in Subsequent Calculations

Tue, 28 Dec 2010 17:35:56 GMT

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>