Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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!
I now tried to use
where not exist (field)
How can i enter combinations of fields?
Like where not exist (field1+field2)
Maybe if you try something like
Where not exists(field) and not exists(field2)
instead of your expression?
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;
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.
That would probably exclude everything with either FIELD 1 OR FIELD 2 right?
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
Yes, i did it your way and now it seems to work. Thank you!