Calling a value from a preceding table

[#1]

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:

     Load * Inline

     [Item,Factor

     Apples,0.1

     Oraange,0.12

     Mango,0.19

     Banana,0.11

     Kiwi,0.14];

TableY:

     Load *, Price*Factor as FactorPriced;

     Load * Inline

     [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?

Many thanks in advance

C

[giakoum]

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.

[#3]

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

[#4]

TABLE X:

     Load * Inline [

     Item,Factor

     Apples,0.1

     Oraange,0.12

     Mango,0.19

     Banana,0.11

     Kiwi,0.14];

Concatenate (TABLE X)

     Load * Inline [

     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;

[giakoum]

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:

     Load * Inline

     [Item,Factor

     Apples,0.1

     Oraange,0.12

     Mango,0.19

     Banana,0.11

     Kiwi,0.14];

Left Join (TableX)

     Load * Inline

     [Item,Price

     Apples,1.32

     Oraange,1.23

     Mango,1.95

     Banana,1.10

     Kiwi,1.45];

TableFinal:

LOAD

          Item,

          Price,

          Factor,

          Price*Factor as FactorPriced

Resident TableX;

DROP Table TableX;

[#6]

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

[swuehl · Accepted solution]

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