topic How to remove duplicates in QlikView

How to remove duplicates

Tue, 30 Jul 2013 17:45:09 GMT

Hi All,

Can anyone help me with romving duplicate records.

Attached is the test file. My team wants to see only one record if either of Edit permssion or Visible permission is 1. I am getting Duplicate records right now.Please help.With Regards,Vikas

PLT_TABLE	UI_FIELDLABEL	DISPLAYCONFIG_CUSTOMVALUE	DATADICTIONARY_CUSTOMVALUE	ORIGINALFIELDNAME	ORIGINALLABEL	FieldAliasName	VISIBLEPERMISSION	EDITPERMISSION
DISCLOSUREDATA9	Color	Color	COLOR	COLOR	COLOR	DS9$COLOR	1	0
DISCLOSUREDATA9	Color	Color	COLOR	COLOR	COLOR	DS9$COLOR	1	1

Re: How to remove duplicates

Gysbert_Wassenaar — Tue, 30 Jul 2013 17:52:49 GMT

You can use a load distinct as long as you don't load the two xxxPERMISSION fields. Otherwise the records will not be duplicates since these xxxPERMISSION fields are where the records differ.

MyTable:

LOAD DISTINCT TABLE,

UI_FIELDLABEL,

DISPLAYCONFIG_CUSTOMVALUE

FROM

test.xls

(biff, embedded labels, table is [Sheet1$])

where VISIBLEPERMISSION = 1 or EDITPERMISSION = 1;

Edit: I'm not sure what you mean with 'either of Edit permssion or Visible permission is 1'. Do you mean that both can be 1 or only one of the two fields should be 1? In the latter case you'd need another where clause:

where (VISIBLEPERMISSION = 1 and EDITPERMISSION = 0) or (VISIBLEPERMISSION = 0 and EDITPERMISSION = 1)

Re: How to remove duplicates

Tue, 30 Jul 2013 18:50:05 GMT

HI Gysbert,

Thanks for the reply..but this is not helping.I want to say that only one of the two field (Edit permssion or Visible permission) needs to be 1.

I am loading this script but

Load

*;

SELECT [ORIGINALFIELDNAME]

            ,[UI_FIELDLABEL]

      ,[FieldAliasName]

      ,[VISIBLEPERMISSION]

      ,[EDITPERMISSION]

FROM Test

where (VISIBLEPERMISSION = 1 and EDITPERMISSION = 0) or (VISIBLEPERMISSION = 0 and EDITPERMISSION = 1);

But the above code is not giving exact result.

Regards,

Vikas

Re: How to remove duplicates

Gysbert_Wassenaar — Tue, 30 Jul 2013 19:04:42 GMT

Of course not. You seem to have missed what I posted:

You can use a load distinct as long as you don't load the two xxxPERMISSION fields. Otherwise the records will not be duplicates since these xxxPERMISSION fields are where the records differ.

Your code loads the xxxPERMISSION fields and that means the records are made unique by these two fields. So you still get distinct records. There are no duplicate records. And that means you get the result you get. And if you don't want that then you have to leave out those two fields.

edit:

only one of the two field (Edit permssion or Visible permission) needs to be 1.

needs or must?

Re: How to remove duplicates

Tue, 30 Jul 2013 19:12:44 GMT

Thanks a lot this helped.