Load records where Sum from one table > than Sum from another...

[#1]

Dear All,

it's my first post on this community. 

I have three tables. One is "Investment", second is "Potential sales" and third is "Real sales".

I want to select only these "Investments", where Sum(Potential_sales.volume) > Sum(Real_sales.volume).

Potential sales is connected with key to Investment.Key, the same 'Real sales.

How can I do this? Thank you in advance.

[sebastiandperei]

Hi,

try with Sum ({<Investment.Field={"=sum(Potential_sales.volume)>sum(Real_sales.volume)"}>} Investment.Field)

If it doesn't work, would be better that you send a reduced copy of the qvw.

[#3]

Hi, Sebastian, I want to deal with this using script. Can I use this expression after "WHERE Sum(..."?

Unfortunately I cannot sen you even reduced file...

When I want to use after "WHERE" I get an error... field (from another table) doesn't exists...

[Anonymous]

Could you just paste in your load script ?

[#5]

Hi Bill,

I cannot copy whole of it, there are many tabs... if I may, I will paste only shortened version:

Investments:

LOAD

    ROW as Investment.Key,

    investment.name,

    ... and so on...

FROM [xxxx.QVD] (qvd)

WHERE

Sum {(Investment.Key distinct ={"Sum if(Potential.Unit='m³',Potential.Quantity)) > Sum ( if(RealSales.Quantity)"}>} Investment.Key);

Potentials:

Concatenate(organisationtable)

LOAD

Quantity,

Unit,

Investment_ID as Investment.Key,

... and so on...

FROM [yyyy.qvd] (qvd);

RealSales:

Concatenate(organisationtable)

LOAD

    investment as Investment.Key,

Quantity,

Unit

From [zzzz.qvd] (qvd);

[maxgro]

load investment in a temporary table

join the temporary table with Potentials and then RealSales using ...Key, so you have the field you need for filter in the same row

filter temporary table and add to final table

Tmp:

LOAD

    ROW as Key,

    investment.name,

    ... and so on...

FROM [xxxx.QVD] (qvd)

// join Potentials

left join (Tmp)

LOAD

Quantity as PotentialQuantity,

Investment_ID as Key,

... and so on...

FROM [yyyy.qvd] (qvd);

// join with RealSales

left join (Tmp)

LOAD

Quantity as RealSalesQuantity,

Investment_ID as Key,

... and so on...

FROM [yyyy.qvd] (qvd);

Investments:

noconcatenate load

     ....

resident Tmp

where PotentialQuantity > realQuantity;

drop tableTmp;

[#7]

Hi Massimo, thank you, but I cannot change this table into temp... there are too many dependences on "Investment" table. Can I somehow create this temp table and use values, ex. (1 - where potential>sales, 0 - where potential<sales)?

Maybe there is a way to do it not in the script... can I include on report only values where Sum(Potential_sales.volume) > Sum(Real_sales.volume).

Later, I want to use report and loop it over Investments where Potential > RealSales.

[sebastiandperei]

Krzy, the line i sent is useful in a Graph expression, not for the script.

At the first post, you said that ".. i want to SELECT... ", and this is not possible in the script.

Do you want to exclude ALL data of only these "Investments", where Sum(Potential_sales.volume) > Sum(Real_sales.volume).?? If yes, you could do it in script.

Do you want to show these "investments" in a particular object (like table, graph)?? If yes, you CAN'T do it in script. You could use the expression that I sent.

Please, tell us what is exactly you want to do.