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

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

daveatkins — Thu, 18 Dec 2014 19:18:57 GMT

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?

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

kamal_sanguri — Thu, 18 Dec 2014 20:39:04 GMT

Probably this will help..

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

kamal_sanguri — Thu, 18 Dec 2014 20:40:41 GMT

Also the file which I used...

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

saurabh5 — Thu, 18 Dec 2014 21:46:28 GMT

you can create a flag in your table like:

(count(Name)>1,1,0) as flag resident table..

this will create a flag which will identify which are duplicate and which are not.

Regards

Saurabh

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

daveatkins — Fri, 19 Dec 2014 19:50:18 GMT

won't that just be 1 for all rows, since it will simply count the total number of names?

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

kamal_sanguri — Fri, 19 Dec 2014 19:51:47 GMT

Did you check the app that I attached?

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

kamal_sanguri — Fri, 19 Dec 2014 19:53:00 GMT

download the excel file put the data and then run the app after changing the path in QVW.. let me know..

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

saurabh5 — Fri, 19 Dec 2014 20:21:18 GMT

it will count the names, and flag with number '1' if it repeating more than once in your column,and if the name is just once in the column then it will flag it as '0'.

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

daveatkins — Fri, 19 Dec 2014 21:12:32 GMT

When I tried this, it counted all rows; it did not group by the name/account. To see what was happening, I simplified to:

load count(Account) as NumberOfRepeats

Resident Main;

this results in adding a new column which contains the count of all Accounts--the same number for each row

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

kamal_sanguri — Fri, 19 Dec 2014 21:21:51 GMT

I guess you didn't try this...

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

kamal_sanguri — Fri, 19 Dec 2014 21:24:42 GMT

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.

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

daveatkins — Fri, 19 Dec 2014 21:30:03 GMT

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.

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

kamal_sanguri — Fri, 19 Dec 2014 21:36:18 GMT

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

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

daveatkins — Fri, 19 Dec 2014 21:40:18 GMT

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

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

kamal_sanguri — Fri, 19 Dec 2014 21:44:28 GMT

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..

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

daveatkins — Fri, 19 Dec 2014 22:02:09 GMT

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.