Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

Exclude Fields from Distinct

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Exclude Fields from Distinct

if I understand

t:

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;

t:

load *  From t.qvd (qvd)

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

//DROP Field checkfield;

9 Replies

Re: Exclude Fields from Distinct

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

Not applicable

Re: Exclude Fields from Distinct

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!

Not applicable

Re: Exclude Fields from Distinct

I now tried to use

where not exist (field)

How can i enter combinations of fields?

Like where not exist (field1+field2)

Employee
Employee

Re: Exclude Fields from Distinct

Maybe if you try something like

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

instead of your expression?

MVP
MVP

Re: Exclude Fields from Distinct

if I understand

t:

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;

t:

load *  From t.qvd (qvd)

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

//DROP Field checkfield;

Not applicable

Re: Exclude Fields from Distinct

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.

Not applicable

Re: Exclude Fields from Distinct

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

MVP
MVP

Re: Exclude Fields from Distinct

this is the result of the script I posted

1.jpg

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

Not applicable

Re: Exclude Fields from Distinct

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

Community Browser