topic Re: aggr Count in script in QlikView

aggr Count in script

Thu, 12 Mar 2015 06:29:28 GMT

Hi All,

I have a performance issue in my QV App,Because i wrote the some of expressions in my app like :

sum(aggr(if((Value)<Target,count(CustomersID)),CustomersID,Country,Site,Brand,Region,Division,Product,Month,Target))

i am getting the correct values when i am using the above expression in charts but the performance in very low.

In my script i am giving the association to both the tables.

Can any one help me on this how to handle this in script level.Following is my tables fileds:

Fact:

LOAD CustomersID,

Site,

Product,

Country,

Region,

Division,

Brand,

Month,

Value

FROM

[........];

Target:

LOAD CustomersID,

Target,

Target_Cal

FROM

[........];

I want all the records from the both the tables which is not matching

Thanks in advance

Re: aggr Count in script

Thu, 12 Mar 2015 06:36:50 GMT

sum(aggr(if((Value<Target,count(CustomersID))

,CustomersID,Country,Site,Brand,Region,Division,Product,Month,Target))

Fact:

LOAD CustomersID,

Site,

Product,

Country,

Region,

Division,

Brand,

Month,

Value

FROM

[........];

left join(Fact)

//Target:

LOAD CustomersID,

Target,

Target_Cal

FROM

[........];

Final_Fact:

load *,

if((Value<Target,count(CustomersID)) as ResultFld

resident Fact group by CustomersID,Country,Site,Brand,Region,Division,Product,Month,Target;

drop table Fact;

Re: aggr Count in script

jonathandienst — Thu, 12 Mar 2015 06:38:51 GMT

Do you need an Aggr() here? This is mostly for nesting aggregation functions like sum() , avg() or count().

In your case, this should do:

sum(if(Value)<Target,count(CustomersID))

If the granularity of the data is similar and the CustomerID values in Target are unique, consider joining the Target table to the Fact table. This will significantly improve the calculation time.

HTH

Jonathan

Re: aggr Count in script

aveeeeeee7en — Thu, 12 Mar 2015 06:40:28 GMT

Try something like this:

Target:
MAPPING LOAD CustomersID,
Target

FROM

[........];

Fact:
LOAD CustomersID,
     Site,
     Product,
     Country,
     Region,
     Division,
     Brand,
     Month,
     Value,
     Applymap('Target',CustomersID,'NA') AS Target
FROM

[........];

Groupby_Table:

Use above table & use Sum & Count Functions. Group By them using the number of fields according to which you want the desired result.

Resident Fact Table;

Regards

Av7eN

Re: aggr Count in script

Thu, 12 Mar 2015 08:06:57 GMT

Hi,

In scripting you will put same as like this. There are associated with CustomersID.

Fact:

LOAD

CustomersID,

Site,

Product,

Country,

Region,

Division,

Brand,

Month,

Value

FROM

[........];

Target:

LOAD

CustomersID,

Target,

Target_Cal

FROM

[........];

Re: aggr Count in script

simenkg — Thu, 12 Mar 2015 08:18:30 GMT

There are a few ways to do this.

Target:

Load

CustomersID,

Target,

Target_Cal

From ....;

mapTarget:

mapping load

CustomersID,

Target

resident Target;

Fact:

LOAD

CustomersID,

Site,

Product,

Country,

Region,

Division,

Brand,

Month,

Value,

if(ApplyMap('mapTarget',CustomersID,null()) >Value,1,0) as ValidFlag

FROM

[........];

Then your expression becomes:
Count({<ValidFlag={1}>} CustomersID)

This should make the calculations very fast.

Another way to do it is by join:

Fact_t1:

Load * from [....]

left join(Fact_t1)

load * from Target...;

Noconcatenate

Fact:

load *, if(Target>Value, 1,0) as ValidFlag

resident Fact_t1;

Drop table Fact_t1;

Re: aggr Count in script

Fri, 13 Mar 2015 09:44:46 GMT

Thank you all for your replies

Hi Simen,

thanks for explaining good improving code.