Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Not applicable

Show Me the Frequnecy Mr. AGGR

Hi all,

I have a table whoch consisits of 14M records, this table contains duplicates called ID.

I want to find out how many of ID's appear on a table. To achieve this i have created a pivot table

as an expression I'm currently using a pivot table with an expression call 'Activity' with a  (DISTINCT ID) as an expression and a calculated dimension aggr(Count(ID),ID) .

EG

123456789101112131415
3334423423428756143338000576630004772114634231141

So, as we can see on this table, 333442 ID's has appeard once, where as 4 ID's have been counted 14 times.

this works fine, but its SO SLOW! and very memory intensive.

the records count is 14M by 5 Variables. I've tried doing something in the scipt like this

LOAD Distinct
ID
,
ID as IDdistinct,
sum(Cnt) as CountPerID
Resident TABLE
group by ID;

But, this way not dynamic at all, if my users select a spefic product, then this table needs to recaclulate based on that product and so on.

Does anyone know any alternatives or faster ways of doing this.

thanks,

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Show Me the Frequnecy Mr. AGGR

Hi Matthew,

You need to create an new straight table with a calculated dimension of =Aggr(Count(ID),ID) and and expression of Count(Distinct ID).

That should give you what you need - see attached.

Hope this helps,

Jason

View solution in original post

5 Replies
Highlighted
Partner
Partner

Re: Show Me the Frequnecy Mr. AGGR

Hi Matthew,

You need to create an new straight table with a calculated dimension of =Aggr(Count(ID),ID) and and expression of Count(Distinct ID).

That should give you what you need - see attached.

Hope this helps,

Jason

View solution in original post

Highlighted
Not applicable

Re: Show Me the Frequnecy Mr. AGGR

Thank you so much, this does work a treat. The only niggle is that I wish I could have the table going from left to right as it would be much heater in my app.

Highlighted
Not applicable

Re: Show Me the Frequnecy Mr. AGGR

one more question around this.

i have a flag in the data model 'Y' to say if the record is LIVE, there will be some ID's where the flag is blank and i would like to also have a zero on the table to represent zero activity on that ID.

how can that AGGR statement be adjusted to included the 0?

Highlighted
Not applicable

Re: Show Me the Frequnecy Mr. AGGR

Anyone got any ideas on how i can achieve this, ithe table is basically a fact table of how many people have been contacted and i want the 0 to represent how many havent.

Highlighted
Partner
Partner

Re: Show Me the Frequnecy Mr. AGGR

I don't think anyone will see this question Matthew as the thread is already marked as closed with a correct answer. I'll try and have a look for you later but in the meantime you may want to open another question.

Jason