Show Me the Frequnecy Mr. AGGR

[#1]

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

1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
333442	34234	28756	14333	8000	5766	3000	477	211	46	34	23	11	4	1

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,

[Jason_Michaelides · Accepted solution]

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

[#3]

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.

[#4]

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?

[#5]

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.

[Jason_Michaelides]

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