Exclude Fields from Distinct

[#1]

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

[alexandros17]

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

[#3]

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!

[#4]

I now tried to use

where not exist (field)

How can i enter combinations of fields?

Like where not exist (field1+field2)

[Agis-Kalogiannis]

Maybe if you try something like

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

instead of your expression?

[maxgro · Accepted solution]

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;

[#7]

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.

[#8]

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

[maxgro]

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

[#10]

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