Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;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
Thanks Or,
That worked well - saved me a lot of experrimentation.
Greg
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
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
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>