topic expression for unique values in QlikView

expression for unique values

Mon, 21 Jun 2010 17:46:34 GMT

Hi,

I have got following data into the table box.

email date

ab@a.com 12/12/2010

bd@mail.com 13/12/2010

ac@y.com 13/12/2010

bd@mail.com 13/12/2010

what expression will work to display only uniqe records (remove duplicates)?

Thanks,

expression for unique values

disqr_rm — Mon, 21 Jun 2010 17:51:18 GMT

Replace tablebox with a straight table (chart), put both fields as dimension, add a 1 as dummy expression, under presentation tab hide this dummy expression, and it should work just fine.

expression for unique values

Mon, 21 Jun 2010 18:04:38 GMT

Hi Rakesh,

It didn't work for me or i may following different approch for these steps.

is it possible you to share the screen shot as i am using personal addition?

Cheers

expression for unique values

disqr_rm — Mon, 21 Jun 2010 18:20:35 GMT

See enclosed. You should be able to open with your personal edition.

Hope this helps.

expression for unique values

Mon, 21 Jun 2010 23:34:47 GMT

The table box should not show the records if they are EXACT duplicates. So, if they appear with another field that is not the same, or have a slightly different time stamp, have a wacky leading, or traling space character, etc then you'll see them print.

If the above data is exactly as it apears in your table box, and those are the only fields, then it should not show duplicates.

Additionally, table boxes can not contain expressions.

expression for unique values

Tue, 22 Jun 2010 09:12:20 GMT

After turning a data to chart straight table, applying a 1 dummy expression and hide the dummy cloumn; that would not allow to dislpay only unique records.

Any better expression to resolve?

Thanks,

expression for unique values

Tue, 22 Jun 2010 11:28:31 GMT

Can someone help me out on initail query?

Thanks

expression for unique values

Tue, 22 Jun 2010 13:01:16 GMT

Thats because your date field contais, not only a date as it shows, but a timestamp.

You will need to make it a "date only" field.

You can do this in the script, using a numeric function called floor().

Ex:

Table:

Load email, date(floor(DateFieldName)) as date from table "X.qvd";

After that, your will be able to show the content without the duplicatesz.

Cheers,

Bruno

expression for unique values

Tue, 22 Jun 2010 13:14:23 GMT

Hi Bruno,

Thanks for the reply but originally date fields itself have date format. (no timestamp).

I am sure there is a clever way to remove duplicates. Any other thoughts?

Best,

expression for unique values

johnw — Tue, 22 Jun 2010 23:30:39 GMT

As others have said, a table box will not display duplicates. I've loaded the exact data you posted, and created a table box. Have a look. The duplicates are removed automatically by QlikView. I have to add an ID field, and include it in the table box, if I want to see the duplicates.

I can't solve a problem that doesn't exist. Please post an example that DOES show duplicates, and maybe we'll get somewhere.