topic Calling a value from a preceding table in QlikView

Calling a value from a preceding table

Tue, 07 Aug 2012 14:12:31 GMT

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

Re: Calling a value from a preceding table

giakoum — Tue, 07 Aug 2012 14:14:47 GMT

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

Tue, 07 Aug 2012 14:16:42 GMT

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

Tue, 07 Aug 2012 14:17:00 GMT

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;

Re: Calling a value from a preceding table

giakoum — Tue, 07 Aug 2012 14:22:09 GMT

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;

Re: Calling a value from a preceding table

Tue, 07 Aug 2012 14:25:18 GMT

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

Re: Calling a value from a preceding table

swuehl — Tue, 07 Aug 2012 14:25:45 GMT

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