topic Re: Exclude Fields from Distinct in Qlik Learning Discussions

Exclude Fields from Distinct

Thu, 12 Feb 2015 16:22:14 GMT

Hi!

Is it possible to exclude certain fields when using Load Distinct?

I have a table and i want to remove duplicates which are the same except for 3 fields can i exclude them?

Thanks!

Julian

Re: Exclude Fields from Distinct

alexandros17 — Thu, 12 Feb 2015 16:27:10 GMT

If your field values are for instance A,B,C for the field FLD then:

Load distinct FLD

...

Resident ....

Where FLD <> A and FLD <> B and FLD <> C

concatenate

Load FLD

...

Resident ....

Where FLD =A or FLD = B or FLD = C

let me know

Re: Exclude Fields from Distinct

Thu, 12 Feb 2015 16:34:03 GMT

Hey,

thanks for the fast answer!

I probably didnt explain myself right, so heres an example

I have a table with:

FIELD 1 , FIELD 2, FIELD 3, FIELD 4, FIELD 4, FIELD 5

what i do now is

load distinct

FIELD 1 as FIELD_1_X (that has other reasons)

FIELD 2 as FIELD_2_X

FIELD 3 as FIELD_3_X

FIELD 4 as FIELD_4_X

FIELD 5 as FIELD_5_X

Resident.....

Now for example it is possible i have an entry where all fields are the same except FIELD3, since it isnt the same entry overall it will be loaded. When i remove FIELD3 from the expression it works obviously.

I want it loaded but excluded in the distinct expression.

Thanks!

Re: Exclude Fields from Distinct

Tue, 17 Feb 2015 14:00:40 GMT

I now tried to use

where not exist (field)

How can i enter combinations of fields?

Like where not exist (field1+field2)

Re: Exclude Fields from Distinct

Agis-Kalogiannis — Tue, 17 Feb 2015 14:48:00 GMT

Maybe if you try something like

Where not exists(field) and not exists(field2)

instead of your expression?

Re: Exclude Fields from Distinct

maxgro — Tue, 17 Feb 2015 19:56:04 GMT

if I understand

load

FIELD1 & '|' & FIELD2 & '|' & FIELD4 & '|' & FIELD5 as checkfield // field used for check distinct

inline [

FIELD1,FIELD2,FIELD3,FIELD4,FIELD5

1,1,x,1,1

2,2,y,2,2,

1,1,z,1,1

2,2,w,2,2

1,2,a,3,4

1,2,b,3,5

];

STORE t into t.qvd (qvd);

DROP Table t;

load * From t.qvd (qvd)

Where not exists (checkfield); // only load distinct FIELD1..2..4..5

//DROP Field checkfield;

Re: Exclude Fields from Distinct

Thu, 19 Feb 2015 11:13:28 GMT

Thank you! I had a very similar idea yesterday.

Doesn't work in my case yet. Why do you drop the first table beforehand?

I used

t_2:

load *

Resident t

Where not exists (checkfield);

Without dropping t first. Doesnt work though. I just get nothing.

Re: Exclude Fields from Distinct

Thu, 19 Feb 2015 11:17:18 GMT

That would probably exclude everything with either FIELD 1 OR FIELD 2 right?

Re: Exclude Fields from Distinct

maxgro — Thu, 19 Feb 2015 11:36:05 GMT

this is the result of the script I posted

If I don't drop the table the t

Where not exists (checkfield)

doesn't work because there are the values 1|1|1|1 , .... in table t

Re: Exclude Fields from Distinct

Thu, 19 Feb 2015 11:38:33 GMT

Yes, i did it your way and now it seems to work. Thank you!