topic Count Distinct row table in QlikView

Count Distinct row table

ariel_klien — Sat, 16 Nov 2024 02:25:10 GMT

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

Re: Count Distinct row table

Arthur_Fong — Tue, 24 Sep 2019 00:56:56 GMT

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

Re: Count Distinct row table

marcus_sommer — Tue, 24 Sep 2019 09:23:36 GMT

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

Re: Count Distinct row table

Brett_Bleess — Wed, 02 Oct 2019 14:36:41 GMT

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

Re: Count Distinct row table

ariel_klien — Thu, 03 Oct 2019 06:31:46 GMT

Still working on the solution

Re: Count Distinct row table

Brett_Bleess — Thu, 03 Oct 2019 10:58:41 GMT

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