topic Query in QlikView

Query

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Query

Thu, 23 Jun 2016 10:04:23 GMT

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

Re: Query

avinashelite — Thu, 23 Jun 2016 10:10:32 GMT

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;

Re: Query

Kushal_Chawda — Thu, 23 Jun 2016 10:18:49 GMT

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)

Re: Query

effinty2112 — Thu, 23 Jun 2016 10:29:08 GMT

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

Re: Query

antoniotiman — Thu, 23 Jun 2016 10:29:37 GMT

Try

Table2:

Load * from Table2;

Left Join (Table2)

Load * from Table1;

And in Your Pivot Table You have aggregations

Regards,

Antonio

Re: Query

Mon, 27 Jun 2016 11:30:06 GMT

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

thanks again!