Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Count Distinct row table

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

 

Labels (4)
5 Replies
Highlighted
Partner
Partner

Re: Count Distinct row table

There you go:

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

Then in UI,

clipboard_image_0.png

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

 

Highlighted
MVP & Luminary
MVP & Luminary

Re: Count Distinct row table

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

Highlighted
Digital Support
Digital Support

Re: Count Distinct row table

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
Specialist
Specialist

Re: Count Distinct row table

Still working on the solution 

Highlighted
Digital Support
Digital Support

Re: Count Distinct row table

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.