Query

[#1]

Hi!

it's probably a trivial question but i'm not sure how this syntax works in QV:

I have 2 tables:

table 1:

ClientID

ProductCity

ProductID

Gross

table 2:

ProductCIty

I would like to sum the Gross only for the cities that are found in table 2 and group by clientID and productCity.

what's the best way to do that?

thanks

[#2]

Hi,

You can do it by writing Load Script in below way:

DATA:

LOAD

   ProductCIty

FROM Table2

Left Join

LOAD

ClientID,

ProductCity

Gross

from Table1

[avinashelite]

if you want to do it in script try like this

Table1:

LOAD ClientID,

ProductCity,

ProductID,

Gross

from table1;

Join

LOAD

'Table2' as Identifier,

ProductCIty as ProductCity

from table2;

Result:

LOAD sum(Gross) as Result_Gross,

ClientID as Result_ClientID,

ProductCity as Result_ProductCity

where Identifier='Table2'

group by

ClientID,

ProductCity;

[Kushal_Chawda · Accepted solution]

Create a Flag in script like below

Table2:

LOAD ProductCity as ProductCity2

FROM tab2;

Table1:

LOAD

ClientID,

if(exists(ProductCity2,ProductCity) ,1) as ProdutCityFlag

ProductCity

ProductID

Gross

From table1;

Now you can use the expression

=Sum({<ProdutCityFlag={1}>}Gross)

[effinty2112]

Hello,

     Using Table 2 to make a mapping I think is a neat way to do this.

MappingCity:

Mapping

LOAD

ProductCity,

'Yes'

Resident Table2;

Left Join (Table1)

Load

DISTINCT

ProductCity,

ApplyMap('MappingCity',ProductCity,'No') as Flag

Resident Table 1;

Now you can select 'Yes' in the flag field

or write your expression like:

Sum(If(Flag = 'Yes',Gross))

or write your expression like:

Sum({$<Flag = {'Yes'}>Gross}

Cheers

Andrew

[antoniotiman]

Try

Table2:

Load * from Table2;

Left Join (Table2)

Load * from Table1;

And in Your Pivot Table You have aggregations

Regards,

Antonio

[#7]

thank you all for your replies. my intention was to create this in the script itself.

thanks again!