topic Re: sum() with values from another table in QlikView

sum() with values from another table

Tue, 01 Jul 2014 07:26:13 GMT

Hi guys,

how can I use a columm, which i created new from another table like this

Table1: Amount,

Date,

If(Amount, Amount*(-1)) as NEW.Amount

Table2:

Load Amount,

Date,

sum(NEW.Amount) as SUM.SELL where (.X..) group by ..

SQL Select Date, Amount ... FROm ...

Table3:

Load Amount,

Date,

sum(NEW.Amount) as SUM.BUYwhere (.Y..) group by ..

SQL Select Date, Amount ... FROm ...

the NEW.Amount is now in the database, so how can I use it across the tables?

Re: sum() with values from another table

Tue, 01 Jul 2014 07:41:14 GMT

I get an error if I use that ..

"New.Amount" not found SQL SELECT...FROM..

Re: sum() with values from another table

alexandros17 — Tue, 01 Jul 2014 07:43:38 GMT

In the script you may use a field only if it is contained in the table so if you need this you habìve to join somehow the tables.

Re: sum() with values from another table

Anonymous — Tue, 01 Jul 2014 07:47:42 GMT

You can use NEW.Amount by doing resident load of table 1. See below,

Table1: Amount,

Date,

If(Amount, Amount*(-1)) as NEW.Amount

Table2:

Load Amount,

Date,

sum(NEW.Amount) as SUM.SELL

Resident Table1

where (.X..) group by ..;

Table3:

Load Amount,

Date,

sum(NEW.Amount) as SUM.BUY

Resident Table1

where (.Y..) group by ..;

Re: sum() with values from another table

Tue, 01 Jul 2014 07:50:15 GMT

But it wont work if I use this:

CONNECT TO DB;

Load Date,

Amount,

If(Amount, Amount*(-1)) as NEW.Amount,

sum(New.Amount) as SUM01 WHERE (..X) Group By Date, Amount, NEW.Amount

SQL SELECT Date, Amount

FROM DB.DB1

I would also get en error with: "not found the field New.Amount" in sql..