Count Distinct row table

[ariel_klien]

Hello All Experts.

I have a table with millions of rows 
Something like that

call ID	Queue	Queue Type	Devision	CallCenter	CallType	Date	Time
11111	Q1	T1	D1	CC1	CT1	01/01/2020	08:00
11111	Q2	T1	D1	CC1	CT2	01/01/2020	08:00
11111	Q3	T2	D1	CC1	CT3	01/01/2020	08:00
11111	Q4	T3	D1	CC2	CT1	01/01/2020	08:00
11112	Q1	T2	D1	CC2	CT2	02/01/2020	08:00

Today the model store all data. the performance are week.

I though the best way is to make aggr table in the script.

But here is the problem: my KPI for the example is count of calls. 

Today: Count(Distinct Call_Id)  (in the UI)

Queue	Count distinct
Q1	2
Q2	1
Q3	1
Q4	1
Total	5

Queue Type	Count distinct
T1	1
T2	2
T3	1
Total	4

Devision	Count distinct
D1	2
Total	2

Call Center	Count distinct
CC1	1
CC2	2
Total	3

Call Type	Count distinct
CT1	2
CT2	2
CT3	1
Total	5

I want to do this in the script. 

I have 6 Dimensions and for each one I have 10 filters.   

Any thoughts how can I archive that in the script level? 

Thanks 

Ariel

[Arthur_Fong]

There you go:

Apply CrossTable function so that your table can be grouped by using the new columns defined.

Then in UI,

Select the dimension value (eg: CallCenter) to get the count distinct.

I am using sum function at UI as I did the count at script level.

If you want 6 different tables, use set analysis to split each dimension values into separate tables.

Refer qvw attached for reference.

Thanks and regards,

Arthur Fong

[marcus_sommer]

Usually are count(distinct FIELD) calculations quite fast even with millions of records. Therefore I'm not sure if you could really speed it up with multiple aggregation-tables (6 dimensions * 10 filters = 60 tables ?) which would be also needed to associate within the datamodel (beside the efforts to create them - from the development point of view and also to the load run-times and further to access them in the UI).

Maybe there are other optimizations in the datamodel, for example if it's not a star-scheme or a flat-table.

- Marcus

[Brett_Bleess]

Ariel, did Arthur or Marcus' posts help?  If so, be sure to use the Accept as Solution on the one(s) that did help, and if you did something different, consider posting that and marking it.  If you are still working on things, leave an update.

Regards,
Brett

[ariel_klien]

Still working on the solution 

[Brett_Bleess]

Ariel, you may want to consider attaching the QVW or a sample one with the data model etc., as that is likely the only way you are going to get the help you want on this one...

Regards,
Brett