Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
daveatkins
Partner - Creator III
Partner - Creator III

How to calculate an expression indicating whether the value from a column is repeated in more than one row?

I have a data table containing names of people from different lists, combined in one large list by concatenating multiple data sources. For example, each person might "qualify" to be on the main/master list for multiple reasons and not all columns are duplicates, so this is not a case where I want to eliminate duplicates. I want to add a column in my table chart to indicate if a person is on the list more than once.

Example Data:

Name, Phone, PhoneLocation, Multiple Contact Methods?

Dave, 555-1212, home, yes

Dan, 555-0000, home, no

Dave, 555-7777, work, yes

This example seems silly, but imagine there are many more fields in the list and it is sorted by something unrelated to name or phone number. Is this a simple set analysis count or something?

15 Replies
kamal_sanguri
Specialist
Specialist

count function will count all the values in that column and there is no way you can use countif function in Qlikview.

So you have to use Exist function to see if that value has already been loaded in that column.


daveatkins
Partner - Creator III
Partner - Creator III
Author

yes I tried that example. Is that the only way to do this? The model typically takes 1/2 hour or more to load (millions of rows of data, 60+ columns). I thought I had done this kind of conditional count in an expressions sometime. I would rather not have to move the table from one table to another unless that is the only way to do this.

kamal_sanguri
Specialist
Specialist

For this specific problem.. I don't think there would be any other way, otherwise you would have seen numbers of community member stepped in to help you

daveatkins
Partner - Creator III
Partner - Creator III
Author

OK, thank you for your solution...I will try that and see how it goes with real data.

kamal_sanguri
Specialist
Specialist

If the performance is an Issue, try this.. this should enhance perform at least a bit..

load table store into qvd.. so that it loads faster next time..

daveatkins
Partner - Creator III
Partner - Creator III
Author

yes, definitely. I load two massive SQL queries separately, save to qvd, then load and concatenate them. This helps during testing and development. When the application is deployed however, it will be a realtime dashboard reloading probably hourly.