aggr Count in script

[#1]

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

[#2]

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;

[jonathandienst]

Hi

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

[aveeeeeee7en]

Hi

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

[#5]

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

[........];

[simenkg · Accepted solution]

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;

[#7]

Thank you all for your replies

Hi Simen,

thanks for explaining good improving code.