6 Replies Latest reply: Aug 7, 2012 10:25 AM by Stefan Wühl

# Calling a value from a preceding table

Hi

I want to calculate a value at script level and the values are taken from two different tables.  I'm trying to execute the following script however it doesn't recognise "factor".

TableX:

[Item,Factor

Apples,0.1

Oraange,0.12

Mango,0.19

Banana,0.11

Kiwi,0.14];

TableY:

[Item,Price

Apples,1.32

Oraange,1.23

Mango,1.95

Banana,1.10

Kiwi,1.45];

Can some one point out what it is I'm doing wrong please?

C

• ###### Re: Calling a value from a preceding table

This will not work.

You need to load the tables and left join them so that they become one table and then perform the calculation.

• ###### Re: Calling a value from a preceding table

This is a really simple illustration of something much bigger; is it not possible with out joining the table?

• ###### Re: Calling a value from a preceding table

You can also consider a MAPPING LOAD of your factor table and an ApplyMap() in your second (a classical lookup).

• ###### Re: Calling a value from a preceding table

TABLE X:

Item,Factor

Apples,0.1

Oraange,0.12

Mango,0.19

Banana,0.11

Kiwi,0.14];

Concatenate (TABLE X)

Item,Price

Apples,1.32

Oraange,1.23

Mango,1.95

Banana,1.10

Kiwi,1.45]

WHERE EXISTS Item;

TABLE Y:

NOCONCATENATE

LOAD *, Price * Factor AS FactorPriced RESIDENT TABLE X

DROP TABLE X;

• ###### Re: Calling a value from a preceding table

I dont think so. Concatenate introduced by Felix has the same effect : 1 final table.

but you may left join and keep the original table as well by using the keep prefix.

Anyway, this is a sample script :

TableX:

[Item,Factor

Apples,0.1

Oraange,0.12

Mango,0.19

Banana,0.11

Kiwi,0.14];

Left Join (TableX)

[Item,Price

Apples,1.32

Oraange,1.23

Mango,1.95

Banana,1.10

Kiwi,1.45];

TableFinal:

Item,

Price,

Factor,

Price*Factor as FactorPriced

Resident TableX;

DROP Table TableX;

• ###### Re: Calling a value from a preceding table

Both methods should work fine for your solution, if not attach an example file if you cna and we will respond asap