topic Re: Data issue in back end aggregation in QlikView

Data issue in back end aggregation

saradhi_it — Thu, 02 Mar 2017 06:53:25 GMT

we are planning a search dashboard with the data was huge we did some front aggregations like (count(distinct) in the dashboard but for optimization purpose my manger told me to all the aggregations ,if i did back end aggregations my data was getting doubled

Example : Count(DISTINCT if(cdate>= vStartdate and cdate<=vEnddate and stage_no=1, cookie)) the value was 63130

with aggregationed changes in the backend i used this formula

sum(DISTINCT if(cdate>= vStartdate and cdate<=vEnddate and stage_no=1, Search) the value was 124212

it was doubled and it was wrong , i tried so many changes i commented the stages and cookie columns but the result was same

i'm sharing the code which i used

SEARCH:

LOAD

autonumber(cookie & cdate) as cookie,

date(cdate) as cdate,

MonthName(cdate) as c_Month,

channel,

stage,

stage_no,

Booking_no,

test_ids

FROM

(qvd)

where match(stage_no,'1','5','7','8','9','10')

and cookie <>'Unknown' and match(channel,'SITE','MOBILE');

NoConcatenate

SEARCH1:

LOAD

cookie,

cdate,

channel,

stage,

stage_no,

Booking_no,

test_ids

count(DISTINCT if( stage='1', cookie)) as STAGE1,

Count( DISTINCT if( stage=8, cookie)) as STAGE2,

Count( DISTINCT if( stage=9, cookie)) as STAGE3,

Count( DISTINCT if(stage=10, cookie)) as STAGE4,

Count( DISTINCT if( stage=7, cookie)) as STAGE5,

Resident SEARCH

where match(channel,'SITE') AND len(test_ids)>0

group by

apache_cookie,

cookie,

cdate,

c_Week,

channel,

domain,

stage,

stage_no,

sector,

Booking_no,

test_ids

drop table SEARCH;

Regards

Pardhu

Re: Data issue in back end aggregation

Anil_Babu_Samineni — Thu, 02 Mar 2017 07:10:18 GMT

I don't even feel performance issue while using this on front-end. Can you share your rows in that object

Count(DISTINCT if(cdate>= vStartdate and cdate<=vEnddate and stage_no=1, cookie))

Changed to below and Try from your end then check how index seems in GDB{Governance Dashboard} - {If available}.

Count({<cdate = {">= $(vStartdate) <= $(vEnddate)"}, stage_no={'1'}>} DISTINCT cookie)

The reason i am feeling change is, Aggregation good and works too in script level. But, It effect to the DM

Re: Data issue in back end aggregation

saradhi_it — Thu, 02 Mar 2017 07:18:12 GMT

Actually the data was historical and we are thinking if the data was Aggregation in the backend we are assuming it will decrease dashboard size

Re: Data issue in back end aggregation

Anil_Babu_Samineni — Thu, 02 Mar 2017 07:26:14 GMT

True, I agree for your way.

But, In script i didn't feel any wrong. So, I will explain you one thing

Recently(8 Months back) i did Aggregation on script only. After that i removed that Pivot / Straight objects and used Table box for Aggregation fields simply and then It showing Out of memory with X Mark.

Hope clear and helps to you

Re: Data issue in back end aggregation

saradhi_it — Thu, 02 Mar 2017 09:52:49 GMT

the set analysis was not working showing zero values

Re: Data issue in back end aggregation

Anil_Babu_Samineni — Thu, 02 Mar 2017 10:42:30 GMT

It should work, Can you share the snap where it is not working